1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self) -> t.Any:
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self):
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    @property
 242    def meta(self) -> t.Dict[str, t.Any]:
 243        if self._meta is None:
 244            self._meta = {}
 245        return self._meta
 246
 247    def __deepcopy__(self, memo):
 248        copy = self.__class__(**deepcopy(self.args))
 249        if self.comments is not None:
 250            copy.comments = deepcopy(self.comments)
 251
 252        if self._type is not None:
 253            copy._type = self._type.copy()
 254
 255        if self._meta is not None:
 256            copy._meta = deepcopy(self._meta)
 257
 258        return copy
 259
 260    def copy(self):
 261        """
 262        Returns a deep copy of the expression.
 263        """
 264        new = deepcopy(self)
 265        new.parent = self.parent
 266        return new
 267
 268    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 269        if self.comments is None:
 270            self.comments = []
 271        if comments:
 272            for comment in comments:
 273                _, *meta = comment.split(SQLGLOT_META)
 274                if meta:
 275                    for kv in "".join(meta).split(","):
 276                        k, *v = kv.split("=")
 277                        value = v[0].strip() if v else True
 278                        self.meta[k.strip()] = value
 279                self.comments.append(comment)
 280
 281    def append(self, arg_key: str, value: t.Any) -> None:
 282        """
 283        Appends value to arg_key if it's a list or sets it as a new list.
 284
 285        Args:
 286            arg_key (str): name of the list expression arg
 287            value (Any): value to append to the list
 288        """
 289        if not isinstance(self.args.get(arg_key), list):
 290            self.args[arg_key] = []
 291        self.args[arg_key].append(value)
 292        self._set_parent(arg_key, value)
 293
 294    def set(self, arg_key: str, value: t.Any) -> None:
 295        """
 296        Sets arg_key to value.
 297
 298        Args:
 299            arg_key: name of the expression arg.
 300            value: value to set the arg to.
 301        """
 302        if value is None:
 303            self.args.pop(arg_key, None)
 304            return
 305
 306        self.args[arg_key] = value
 307        self._set_parent(arg_key, value)
 308
 309    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 310        if hasattr(value, "parent"):
 311            value.parent = self
 312            value.arg_key = arg_key
 313        elif type(value) is list:
 314            for v in value:
 315                if hasattr(v, "parent"):
 316                    v.parent = self
 317                    v.arg_key = arg_key
 318
 319    @property
 320    def depth(self) -> int:
 321        """
 322        Returns the depth of this tree.
 323        """
 324        if self.parent:
 325            return self.parent.depth + 1
 326        return 0
 327
 328    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 329        """Yields the key and expression for all arguments, exploding list args."""
 330        for k, vs in self.args.items():
 331            if type(vs) is list:
 332                for v in vs:
 333                    if hasattr(v, "parent"):
 334                        yield k, v
 335            else:
 336                if hasattr(vs, "parent"):
 337                    yield k, vs
 338
 339    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 340        """
 341        Returns the first node in this tree which matches at least one of
 342        the specified types.
 343
 344        Args:
 345            expression_types: the expression type(s) to match.
 346            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 347
 348        Returns:
 349            The node which matches the criteria or None if no such node was found.
 350        """
 351        return next(self.find_all(*expression_types, bfs=bfs), None)
 352
 353    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 354        """
 355        Returns a generator object which visits all nodes in this tree and only
 356        yields those that match at least one of the specified expression types.
 357
 358        Args:
 359            expression_types: the expression type(s) to match.
 360            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 361
 362        Returns:
 363            The generator object.
 364        """
 365        for expression, *_ in self.walk(bfs=bfs):
 366            if isinstance(expression, expression_types):
 367                yield expression
 368
 369    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 370        """
 371        Returns a nearest parent matching expression_types.
 372
 373        Args:
 374            expression_types: the expression type(s) to match.
 375
 376        Returns:
 377            The parent node.
 378        """
 379        ancestor = self.parent
 380        while ancestor and not isinstance(ancestor, expression_types):
 381            ancestor = ancestor.parent
 382        return t.cast(E, ancestor)
 383
 384    @property
 385    def parent_select(self) -> t.Optional[Select]:
 386        """
 387        Returns the parent select statement.
 388        """
 389        return self.find_ancestor(Select)
 390
 391    @property
 392    def same_parent(self) -> bool:
 393        """Returns if the parent is the same class as itself."""
 394        return type(self.parent) is self.__class__
 395
 396    def root(self) -> Expression:
 397        """
 398        Returns the root expression of this tree.
 399        """
 400        expression = self
 401        while expression.parent:
 402            expression = expression.parent
 403        return expression
 404
 405    def walk(self, bfs=True, prune=None):
 406        """
 407        Returns a generator object which visits all nodes in this tree.
 408
 409        Args:
 410            bfs (bool): if set to True the BFS traversal order will be applied,
 411                otherwise the DFS traversal will be used instead.
 412            prune ((node, parent, arg_key) -> bool): callable that returns True if
 413                the generator should stop traversing this branch of the tree.
 414
 415        Returns:
 416            the generator object.
 417        """
 418        if bfs:
 419            yield from self.bfs(prune=prune)
 420        else:
 421            yield from self.dfs(prune=prune)
 422
 423    def dfs(self, parent=None, key=None, prune=None):
 424        """
 425        Returns a generator object which visits all nodes in this tree in
 426        the DFS (Depth-first) order.
 427
 428        Returns:
 429            The generator object.
 430        """
 431        parent = parent or self.parent
 432        yield self, parent, key
 433        if prune and prune(self, parent, key):
 434            return
 435
 436        for k, v in self.iter_expressions():
 437            yield from v.dfs(self, k, prune)
 438
 439    def bfs(self, prune=None):
 440        """
 441        Returns a generator object which visits all nodes in this tree in
 442        the BFS (Breadth-first) order.
 443
 444        Returns:
 445            The generator object.
 446        """
 447        queue = deque([(self, self.parent, None)])
 448
 449        while queue:
 450            item, parent, key = queue.popleft()
 451
 452            yield item, parent, key
 453            if prune and prune(item, parent, key):
 454                continue
 455
 456            for k, v in item.iter_expressions():
 457                queue.append((v, item, k))
 458
 459    def unnest(self):
 460        """
 461        Returns the first non parenthesis child or self.
 462        """
 463        expression = self
 464        while type(expression) is Paren:
 465            expression = expression.this
 466        return expression
 467
 468    def unalias(self):
 469        """
 470        Returns the inner expression if this is an Alias.
 471        """
 472        if isinstance(self, Alias):
 473            return self.this
 474        return self
 475
 476    def unnest_operands(self):
 477        """
 478        Returns unnested operands as a tuple.
 479        """
 480        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 481
 482    def flatten(self, unnest=True):
 483        """
 484        Returns a generator which yields child nodes who's parents are the same class.
 485
 486        A AND B AND C -> [A, B, C]
 487        """
 488        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 489            if not type(node) is self.__class__:
 490                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 491
 492    def __str__(self) -> str:
 493        return self.sql()
 494
 495    def __repr__(self) -> str:
 496        return self._to_s()
 497
 498    def sql(self, dialect: DialectType = None, **opts) -> str:
 499        """
 500        Returns SQL string representation of this tree.
 501
 502        Args:
 503            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 504            opts: other `sqlglot.generator.Generator` options.
 505
 506        Returns:
 507            The SQL string.
 508        """
 509        from sqlglot.dialects import Dialect
 510
 511        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 512
 513    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 514        indent = "" if not level else "\n"
 515        indent += "".join(["  "] * level)
 516        left = f"({self.key.upper()} "
 517
 518        args: t.Dict[str, t.Any] = {
 519            k: ", ".join(
 520                v._to_s(hide_missing=hide_missing, level=level + 1)
 521                if hasattr(v, "_to_s")
 522                else str(v)
 523                for v in ensure_list(vs)
 524                if v is not None
 525            )
 526            for k, vs in self.args.items()
 527        }
 528        args["comments"] = self.comments
 529        args["type"] = self.type
 530        args = {k: v for k, v in args.items() if v or not hide_missing}
 531
 532        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 533        right += ")"
 534
 535        return indent + left + right
 536
 537    def transform(self, fun, *args, copy=True, **kwargs):
 538        """
 539        Recursively visits all tree nodes (excluding already transformed ones)
 540        and applies the given transformation function to each node.
 541
 542        Args:
 543            fun (function): a function which takes a node as an argument and returns a
 544                new transformed node or the same node without modifications. If the function
 545                returns None, then the corresponding node will be removed from the syntax tree.
 546            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 547                modified in place.
 548
 549        Returns:
 550            The transformed tree.
 551        """
 552        node = self.copy() if copy else self
 553        new_node = fun(node, *args, **kwargs)
 554
 555        if new_node is None or not isinstance(new_node, Expression):
 556            return new_node
 557        if new_node is not node:
 558            new_node.parent = node.parent
 559            return new_node
 560
 561        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 562        return new_node
 563
 564    @t.overload
 565    def replace(self, expression: E) -> E:
 566        ...
 567
 568    @t.overload
 569    def replace(self, expression: None) -> None:
 570        ...
 571
 572    def replace(self, expression):
 573        """
 574        Swap out this expression with a new expression.
 575
 576        For example::
 577
 578            >>> tree = Select().select("x").from_("tbl")
 579            >>> tree.find(Column).replace(Column(this="y"))
 580            (COLUMN this: y)
 581            >>> tree.sql()
 582            'SELECT y FROM tbl'
 583
 584        Args:
 585            expression: new node
 586
 587        Returns:
 588            The new expression or expressions.
 589        """
 590        if not self.parent:
 591            return expression
 592
 593        parent = self.parent
 594        self.parent = None
 595
 596        replace_children(parent, lambda child: expression if child is self else child)
 597        return expression
 598
 599    def pop(self: E) -> E:
 600        """
 601        Remove this expression from its AST.
 602
 603        Returns:
 604            The popped expression.
 605        """
 606        self.replace(None)
 607        return self
 608
 609    def assert_is(self, type_: t.Type[E]) -> E:
 610        """
 611        Assert that this `Expression` is an instance of `type_`.
 612
 613        If it is NOT an instance of `type_`, this raises an assertion error.
 614        Otherwise, this returns this expression.
 615
 616        Examples:
 617            This is useful for type security in chained expressions:
 618
 619            >>> import sqlglot
 620            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 621            'SELECT x, z FROM y'
 622        """
 623        assert isinstance(self, type_)
 624        return self
 625
 626    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 627        """
 628        Checks if this expression is valid (e.g. all mandatory args are set).
 629
 630        Args:
 631            args: a sequence of values that were used to instantiate a Func expression. This is used
 632                to check that the provided arguments don't exceed the function argument limit.
 633
 634        Returns:
 635            A list of error messages for all possible errors that were found.
 636        """
 637        errors: t.List[str] = []
 638
 639        for k in self.args:
 640            if k not in self.arg_types:
 641                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 642        for k, mandatory in self.arg_types.items():
 643            v = self.args.get(k)
 644            if mandatory and (v is None or (isinstance(v, list) and not v)):
 645                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 646
 647        if (
 648            args
 649            and isinstance(self, Func)
 650            and len(args) > len(self.arg_types)
 651            and not self.is_var_len_args
 652        ):
 653            errors.append(
 654                f"The number of provided arguments ({len(args)}) is greater than "
 655                f"the maximum number of supported arguments ({len(self.arg_types)})"
 656            )
 657
 658        return errors
 659
 660    def dump(self):
 661        """
 662        Dump this Expression to a JSON-serializable dict.
 663        """
 664        from sqlglot.serde import dump
 665
 666        return dump(self)
 667
 668    @classmethod
 669    def load(cls, obj):
 670        """
 671        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 672        """
 673        from sqlglot.serde import load
 674
 675        return load(obj)
 676
 677    def and_(
 678        self,
 679        *expressions: t.Optional[ExpOrStr],
 680        dialect: DialectType = None,
 681        copy: bool = True,
 682        **opts,
 683    ) -> Condition:
 684        """
 685        AND this condition with one or multiple expressions.
 686
 687        Example:
 688            >>> condition("x=1").and_("y=1").sql()
 689            'x = 1 AND y = 1'
 690
 691        Args:
 692            *expressions: the SQL code strings to parse.
 693                If an `Expression` instance is passed, it will be used as-is.
 694            dialect: the dialect used to parse the input expression.
 695            copy: whether or not to copy the involved expressions (only applies to Expressions).
 696            opts: other options to use to parse the input expressions.
 697
 698        Returns:
 699            The new And condition.
 700        """
 701        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 702
 703    def or_(
 704        self,
 705        *expressions: t.Optional[ExpOrStr],
 706        dialect: DialectType = None,
 707        copy: bool = True,
 708        **opts,
 709    ) -> Condition:
 710        """
 711        OR this condition with one or multiple expressions.
 712
 713        Example:
 714            >>> condition("x=1").or_("y=1").sql()
 715            'x = 1 OR y = 1'
 716
 717        Args:
 718            *expressions: the SQL code strings to parse.
 719                If an `Expression` instance is passed, it will be used as-is.
 720            dialect: the dialect used to parse the input expression.
 721            copy: whether or not to copy the involved expressions (only applies to Expressions).
 722            opts: other options to use to parse the input expressions.
 723
 724        Returns:
 725            The new Or condition.
 726        """
 727        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 728
 729    def not_(self, copy: bool = True):
 730        """
 731        Wrap this condition with NOT.
 732
 733        Example:
 734            >>> condition("x=1").not_().sql()
 735            'NOT x = 1'
 736
 737        Args:
 738            copy: whether or not to copy this object.
 739
 740        Returns:
 741            The new Not instance.
 742        """
 743        return not_(self, copy=copy)
 744
 745    def as_(
 746        self,
 747        alias: str | Identifier,
 748        quoted: t.Optional[bool] = None,
 749        dialect: DialectType = None,
 750        copy: bool = True,
 751        **opts,
 752    ) -> Alias:
 753        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 754
 755    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 756        this = self.copy()
 757        other = convert(other, copy=True)
 758        if not isinstance(this, klass) and not isinstance(other, klass):
 759            this = _wrap(this, Binary)
 760            other = _wrap(other, Binary)
 761        if reverse:
 762            return klass(this=other, expression=this)
 763        return klass(this=this, expression=other)
 764
 765    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 766        return Bracket(
 767            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 768        )
 769
 770    def __iter__(self) -> t.Iterator:
 771        if "expressions" in self.arg_types:
 772            return iter(self.args.get("expressions") or [])
 773        # We define this because __getitem__ converts Expression into an iterable, which is
 774        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 775        # See: https://peps.python.org/pep-0234/
 776        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 777
 778    def isin(
 779        self,
 780        *expressions: t.Any,
 781        query: t.Optional[ExpOrStr] = None,
 782        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 783        copy: bool = True,
 784        **opts,
 785    ) -> In:
 786        return In(
 787            this=maybe_copy(self, copy),
 788            expressions=[convert(e, copy=copy) for e in expressions],
 789            query=maybe_parse(query, copy=copy, **opts) if query else None,
 790            unnest=Unnest(
 791                expressions=[
 792                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 793                ]
 794            )
 795            if unnest
 796            else None,
 797        )
 798
 799    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 800        return Between(
 801            this=maybe_copy(self, copy),
 802            low=convert(low, copy=copy, **opts),
 803            high=convert(high, copy=copy, **opts),
 804        )
 805
 806    def is_(self, other: ExpOrStr) -> Is:
 807        return self._binop(Is, other)
 808
 809    def like(self, other: ExpOrStr) -> Like:
 810        return self._binop(Like, other)
 811
 812    def ilike(self, other: ExpOrStr) -> ILike:
 813        return self._binop(ILike, other)
 814
 815    def eq(self, other: t.Any) -> EQ:
 816        return self._binop(EQ, other)
 817
 818    def neq(self, other: t.Any) -> NEQ:
 819        return self._binop(NEQ, other)
 820
 821    def rlike(self, other: ExpOrStr) -> RegexpLike:
 822        return self._binop(RegexpLike, other)
 823
 824    def __lt__(self, other: t.Any) -> LT:
 825        return self._binop(LT, other)
 826
 827    def __le__(self, other: t.Any) -> LTE:
 828        return self._binop(LTE, other)
 829
 830    def __gt__(self, other: t.Any) -> GT:
 831        return self._binop(GT, other)
 832
 833    def __ge__(self, other: t.Any) -> GTE:
 834        return self._binop(GTE, other)
 835
 836    def __add__(self, other: t.Any) -> Add:
 837        return self._binop(Add, other)
 838
 839    def __radd__(self, other: t.Any) -> Add:
 840        return self._binop(Add, other, reverse=True)
 841
 842    def __sub__(self, other: t.Any) -> Sub:
 843        return self._binop(Sub, other)
 844
 845    def __rsub__(self, other: t.Any) -> Sub:
 846        return self._binop(Sub, other, reverse=True)
 847
 848    def __mul__(self, other: t.Any) -> Mul:
 849        return self._binop(Mul, other)
 850
 851    def __rmul__(self, other: t.Any) -> Mul:
 852        return self._binop(Mul, other, reverse=True)
 853
 854    def __truediv__(self, other: t.Any) -> Div:
 855        return self._binop(Div, other)
 856
 857    def __rtruediv__(self, other: t.Any) -> Div:
 858        return self._binop(Div, other, reverse=True)
 859
 860    def __floordiv__(self, other: t.Any) -> IntDiv:
 861        return self._binop(IntDiv, other)
 862
 863    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 864        return self._binop(IntDiv, other, reverse=True)
 865
 866    def __mod__(self, other: t.Any) -> Mod:
 867        return self._binop(Mod, other)
 868
 869    def __rmod__(self, other: t.Any) -> Mod:
 870        return self._binop(Mod, other, reverse=True)
 871
 872    def __pow__(self, other: t.Any) -> Pow:
 873        return self._binop(Pow, other)
 874
 875    def __rpow__(self, other: t.Any) -> Pow:
 876        return self._binop(Pow, other, reverse=True)
 877
 878    def __and__(self, other: t.Any) -> And:
 879        return self._binop(And, other)
 880
 881    def __rand__(self, other: t.Any) -> And:
 882        return self._binop(And, other, reverse=True)
 883
 884    def __or__(self, other: t.Any) -> Or:
 885        return self._binop(Or, other)
 886
 887    def __ror__(self, other: t.Any) -> Or:
 888        return self._binop(Or, other, reverse=True)
 889
 890    def __neg__(self) -> Neg:
 891        return Neg(this=_wrap(self.copy(), Binary))
 892
 893    def __invert__(self) -> Not:
 894        return not_(self.copy())
 895
 896
 897IntoType = t.Union[
 898    str,
 899    t.Type[Expression],
 900    t.Collection[t.Union[str, t.Type[Expression]]],
 901]
 902ExpOrStr = t.Union[str, Expression]
 903
 904
 905class Condition(Expression):
 906    """Logical conditions like x AND y, or simply x"""
 907
 908
 909class Predicate(Condition):
 910    """Relationships like x = y, x > 1, x >= y."""
 911
 912
 913class DerivedTable(Expression):
 914    @property
 915    def selects(self) -> t.List[Expression]:
 916        return self.this.selects if isinstance(self.this, Subqueryable) else []
 917
 918    @property
 919    def named_selects(self) -> t.List[str]:
 920        return [select.output_name for select in self.selects]
 921
 922
 923class Unionable(Expression):
 924    def union(
 925        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 926    ) -> Unionable:
 927        """
 928        Builds a UNION expression.
 929
 930        Example:
 931            >>> import sqlglot
 932            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 933            'SELECT * FROM foo UNION SELECT * FROM bla'
 934
 935        Args:
 936            expression: the SQL code string.
 937                If an `Expression` instance is passed, it will be used as-is.
 938            distinct: set the DISTINCT flag if and only if this is true.
 939            dialect: the dialect used to parse the input expression.
 940            opts: other options to use to parse the input expressions.
 941
 942        Returns:
 943            The new Union expression.
 944        """
 945        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 946
 947    def intersect(
 948        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 949    ) -> Unionable:
 950        """
 951        Builds an INTERSECT expression.
 952
 953        Example:
 954            >>> import sqlglot
 955            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 956            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 957
 958        Args:
 959            expression: the SQL code string.
 960                If an `Expression` instance is passed, it will be used as-is.
 961            distinct: set the DISTINCT flag if and only if this is true.
 962            dialect: the dialect used to parse the input expression.
 963            opts: other options to use to parse the input expressions.
 964
 965        Returns:
 966            The new Intersect expression.
 967        """
 968        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 969
 970    def except_(
 971        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 972    ) -> Unionable:
 973        """
 974        Builds an EXCEPT expression.
 975
 976        Example:
 977            >>> import sqlglot
 978            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 979            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 980
 981        Args:
 982            expression: the SQL code string.
 983                If an `Expression` instance is passed, it will be used as-is.
 984            distinct: set the DISTINCT flag if and only if this is true.
 985            dialect: the dialect used to parse the input expression.
 986            opts: other options to use to parse the input expressions.
 987
 988        Returns:
 989            The new Except expression.
 990        """
 991        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 992
 993
 994class UDTF(DerivedTable, Unionable):
 995    @property
 996    def selects(self) -> t.List[Expression]:
 997        alias = self.args.get("alias")
 998        return alias.columns if alias else []
 999
1000
1001class Cache(Expression):
1002    arg_types = {
1003        "with": False,
1004        "this": True,
1005        "lazy": False,
1006        "options": False,
1007        "expression": False,
1008    }
1009
1010
1011class Uncache(Expression):
1012    arg_types = {"this": True, "exists": False}
1013
1014
1015class DDL(Expression):
1016    @property
1017    def ctes(self):
1018        with_ = self.args.get("with")
1019        if not with_:
1020            return []
1021        return with_.expressions
1022
1023    @property
1024    def named_selects(self) -> t.List[str]:
1025        if isinstance(self.expression, Subqueryable):
1026            return self.expression.named_selects
1027        return []
1028
1029    @property
1030    def selects(self) -> t.List[Expression]:
1031        if isinstance(self.expression, Subqueryable):
1032            return self.expression.selects
1033        return []
1034
1035
1036class Create(DDL):
1037    arg_types = {
1038        "with": False,
1039        "this": True,
1040        "kind": True,
1041        "expression": False,
1042        "exists": False,
1043        "properties": False,
1044        "replace": False,
1045        "unique": False,
1046        "indexes": False,
1047        "no_schema_binding": False,
1048        "begin": False,
1049        "end": False,
1050        "clone": False,
1051    }
1052
1053
1054# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1055# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1056# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1057class Clone(Expression):
1058    arg_types = {
1059        "this": True,
1060        "when": False,
1061        "kind": False,
1062        "shallow": False,
1063        "expression": False,
1064        "copy": False,
1065    }
1066
1067
1068class Describe(Expression):
1069    arg_types = {"this": True, "kind": False, "expressions": False}
1070
1071
1072class Kill(Expression):
1073    arg_types = {"this": True, "kind": False}
1074
1075
1076class Pragma(Expression):
1077    pass
1078
1079
1080class Set(Expression):
1081    arg_types = {"expressions": False, "unset": False, "tag": False}
1082
1083
1084class SetItem(Expression):
1085    arg_types = {
1086        "this": False,
1087        "expressions": False,
1088        "kind": False,
1089        "collate": False,  # MySQL SET NAMES statement
1090        "global": False,
1091    }
1092
1093
1094class Show(Expression):
1095    arg_types = {
1096        "this": True,
1097        "target": False,
1098        "offset": False,
1099        "limit": False,
1100        "like": False,
1101        "where": False,
1102        "db": False,
1103        "scope": False,
1104        "scope_kind": False,
1105        "full": False,
1106        "mutex": False,
1107        "query": False,
1108        "channel": False,
1109        "global": False,
1110        "log": False,
1111        "position": False,
1112        "types": False,
1113    }
1114
1115
1116class UserDefinedFunction(Expression):
1117    arg_types = {"this": True, "expressions": False, "wrapped": False}
1118
1119
1120class CharacterSet(Expression):
1121    arg_types = {"this": True, "default": False}
1122
1123
1124class With(Expression):
1125    arg_types = {"expressions": True, "recursive": False}
1126
1127    @property
1128    def recursive(self) -> bool:
1129        return bool(self.args.get("recursive"))
1130
1131
1132class WithinGroup(Expression):
1133    arg_types = {"this": True, "expression": False}
1134
1135
1136class CTE(DerivedTable):
1137    arg_types = {"this": True, "alias": True}
1138
1139
1140class TableAlias(Expression):
1141    arg_types = {"this": False, "columns": False}
1142
1143    @property
1144    def columns(self):
1145        return self.args.get("columns") or []
1146
1147
1148class BitString(Condition):
1149    pass
1150
1151
1152class HexString(Condition):
1153    pass
1154
1155
1156class ByteString(Condition):
1157    pass
1158
1159
1160class RawString(Condition):
1161    pass
1162
1163
1164class Column(Condition):
1165    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1166
1167    @property
1168    def table(self) -> str:
1169        return self.text("table")
1170
1171    @property
1172    def db(self) -> str:
1173        return self.text("db")
1174
1175    @property
1176    def catalog(self) -> str:
1177        return self.text("catalog")
1178
1179    @property
1180    def output_name(self) -> str:
1181        return self.name
1182
1183    @property
1184    def parts(self) -> t.List[Identifier]:
1185        """Return the parts of a column in order catalog, db, table, name."""
1186        return [
1187            t.cast(Identifier, self.args[part])
1188            for part in ("catalog", "db", "table", "this")
1189            if self.args.get(part)
1190        ]
1191
1192    def to_dot(self) -> Dot | Identifier:
1193        """Converts the column into a dot expression."""
1194        parts = self.parts
1195        parent = self.parent
1196
1197        while parent:
1198            if isinstance(parent, Dot):
1199                parts.append(parent.expression)
1200            parent = parent.parent
1201
1202        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1203
1204
1205class ColumnPosition(Expression):
1206    arg_types = {"this": False, "position": True}
1207
1208
1209class ColumnDef(Expression):
1210    arg_types = {
1211        "this": True,
1212        "kind": False,
1213        "constraints": False,
1214        "exists": False,
1215        "position": False,
1216    }
1217
1218    @property
1219    def constraints(self) -> t.List[ColumnConstraint]:
1220        return self.args.get("constraints") or []
1221
1222
1223class AlterColumn(Expression):
1224    arg_types = {
1225        "this": True,
1226        "dtype": False,
1227        "collate": False,
1228        "using": False,
1229        "default": False,
1230        "drop": False,
1231    }
1232
1233
1234class RenameTable(Expression):
1235    pass
1236
1237
1238class SwapTable(Expression):
1239    pass
1240
1241
1242class Comment(Expression):
1243    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1244
1245
1246class Comprehension(Expression):
1247    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1248
1249
1250# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1251class MergeTreeTTLAction(Expression):
1252    arg_types = {
1253        "this": True,
1254        "delete": False,
1255        "recompress": False,
1256        "to_disk": False,
1257        "to_volume": False,
1258    }
1259
1260
1261# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1262class MergeTreeTTL(Expression):
1263    arg_types = {
1264        "expressions": True,
1265        "where": False,
1266        "group": False,
1267        "aggregates": False,
1268    }
1269
1270
1271# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1272class IndexConstraintOption(Expression):
1273    arg_types = {
1274        "key_block_size": False,
1275        "using": False,
1276        "parser": False,
1277        "comment": False,
1278        "visible": False,
1279        "engine_attr": False,
1280        "secondary_engine_attr": False,
1281    }
1282
1283
1284class ColumnConstraint(Expression):
1285    arg_types = {"this": False, "kind": True}
1286
1287    @property
1288    def kind(self) -> ColumnConstraintKind:
1289        return self.args["kind"]
1290
1291
1292class ColumnConstraintKind(Expression):
1293    pass
1294
1295
1296class AutoIncrementColumnConstraint(ColumnConstraintKind):
1297    pass
1298
1299
1300class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1301    arg_types = {"this": True, "expression": True}
1302
1303
1304class CaseSpecificColumnConstraint(ColumnConstraintKind):
1305    arg_types = {"not_": True}
1306
1307
1308class CharacterSetColumnConstraint(ColumnConstraintKind):
1309    arg_types = {"this": True}
1310
1311
1312class CheckColumnConstraint(ColumnConstraintKind):
1313    pass
1314
1315
1316class ClusteredColumnConstraint(ColumnConstraintKind):
1317    pass
1318
1319
1320class CollateColumnConstraint(ColumnConstraintKind):
1321    pass
1322
1323
1324class CommentColumnConstraint(ColumnConstraintKind):
1325    pass
1326
1327
1328class CompressColumnConstraint(ColumnConstraintKind):
1329    pass
1330
1331
1332class DateFormatColumnConstraint(ColumnConstraintKind):
1333    arg_types = {"this": True}
1334
1335
1336class DefaultColumnConstraint(ColumnConstraintKind):
1337    pass
1338
1339
1340class EncodeColumnConstraint(ColumnConstraintKind):
1341    pass
1342
1343
1344class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1345    # this: True -> ALWAYS, this: False -> BY DEFAULT
1346    arg_types = {
1347        "this": False,
1348        "expression": False,
1349        "on_null": False,
1350        "start": False,
1351        "increment": False,
1352        "minvalue": False,
1353        "maxvalue": False,
1354        "cycle": False,
1355    }
1356
1357
1358class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1359    arg_types = {"start": True, "hidden": False}
1360
1361
1362# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1363class IndexColumnConstraint(ColumnConstraintKind):
1364    arg_types = {
1365        "this": False,
1366        "schema": True,
1367        "kind": False,
1368        "index_type": False,
1369        "options": False,
1370    }
1371
1372
1373class InlineLengthColumnConstraint(ColumnConstraintKind):
1374    pass
1375
1376
1377class NonClusteredColumnConstraint(ColumnConstraintKind):
1378    pass
1379
1380
1381class NotForReplicationColumnConstraint(ColumnConstraintKind):
1382    arg_types = {}
1383
1384
1385class NotNullColumnConstraint(ColumnConstraintKind):
1386    arg_types = {"allow_null": False}
1387
1388
1389# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1390class OnUpdateColumnConstraint(ColumnConstraintKind):
1391    pass
1392
1393
1394class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1395    arg_types = {"desc": False}
1396
1397
1398class TitleColumnConstraint(ColumnConstraintKind):
1399    pass
1400
1401
1402class UniqueColumnConstraint(ColumnConstraintKind):
1403    arg_types = {"this": False, "index_type": False}
1404
1405
1406class UppercaseColumnConstraint(ColumnConstraintKind):
1407    arg_types: t.Dict[str, t.Any] = {}
1408
1409
1410class PathColumnConstraint(ColumnConstraintKind):
1411    pass
1412
1413
1414# computed column expression
1415# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1416class ComputedColumnConstraint(ColumnConstraintKind):
1417    arg_types = {"this": True, "persisted": False, "not_null": False}
1418
1419
1420class Constraint(Expression):
1421    arg_types = {"this": True, "expressions": True}
1422
1423
1424class Delete(Expression):
1425    arg_types = {
1426        "with": False,
1427        "this": False,
1428        "using": False,
1429        "where": False,
1430        "returning": False,
1431        "limit": False,
1432        "tables": False,  # Multiple-Table Syntax (MySQL)
1433    }
1434
1435    def delete(
1436        self,
1437        table: ExpOrStr,
1438        dialect: DialectType = None,
1439        copy: bool = True,
1440        **opts,
1441    ) -> Delete:
1442        """
1443        Create a DELETE expression or replace the table on an existing DELETE expression.
1444
1445        Example:
1446            >>> delete("tbl").sql()
1447            'DELETE FROM tbl'
1448
1449        Args:
1450            table: the table from which to delete.
1451            dialect: the dialect used to parse the input expression.
1452            copy: if `False`, modify this expression instance in-place.
1453            opts: other options to use to parse the input expressions.
1454
1455        Returns:
1456            Delete: the modified expression.
1457        """
1458        return _apply_builder(
1459            expression=table,
1460            instance=self,
1461            arg="this",
1462            dialect=dialect,
1463            into=Table,
1464            copy=copy,
1465            **opts,
1466        )
1467
1468    def where(
1469        self,
1470        *expressions: t.Optional[ExpOrStr],
1471        append: bool = True,
1472        dialect: DialectType = None,
1473        copy: bool = True,
1474        **opts,
1475    ) -> Delete:
1476        """
1477        Append to or set the WHERE expressions.
1478
1479        Example:
1480            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1481            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1482
1483        Args:
1484            *expressions: the SQL code strings to parse.
1485                If an `Expression` instance is passed, it will be used as-is.
1486                Multiple expressions are combined with an AND operator.
1487            append: if `True`, AND the new expressions to any existing expression.
1488                Otherwise, this resets the expression.
1489            dialect: the dialect used to parse the input expressions.
1490            copy: if `False`, modify this expression instance in-place.
1491            opts: other options to use to parse the input expressions.
1492
1493        Returns:
1494            Delete: the modified expression.
1495        """
1496        return _apply_conjunction_builder(
1497            *expressions,
1498            instance=self,
1499            arg="where",
1500            append=append,
1501            into=Where,
1502            dialect=dialect,
1503            copy=copy,
1504            **opts,
1505        )
1506
1507    def returning(
1508        self,
1509        expression: ExpOrStr,
1510        dialect: DialectType = None,
1511        copy: bool = True,
1512        **opts,
1513    ) -> Delete:
1514        """
1515        Set the RETURNING expression. Not supported by all dialects.
1516
1517        Example:
1518            >>> delete("tbl").returning("*", dialect="postgres").sql()
1519            'DELETE FROM tbl RETURNING *'
1520
1521        Args:
1522            expression: the SQL code strings to parse.
1523                If an `Expression` instance is passed, it will be used as-is.
1524            dialect: the dialect used to parse the input expressions.
1525            copy: if `False`, modify this expression instance in-place.
1526            opts: other options to use to parse the input expressions.
1527
1528        Returns:
1529            Delete: the modified expression.
1530        """
1531        return _apply_builder(
1532            expression=expression,
1533            instance=self,
1534            arg="returning",
1535            prefix="RETURNING",
1536            dialect=dialect,
1537            copy=copy,
1538            into=Returning,
1539            **opts,
1540        )
1541
1542
1543class Drop(Expression):
1544    arg_types = {
1545        "this": False,
1546        "kind": False,
1547        "exists": False,
1548        "temporary": False,
1549        "materialized": False,
1550        "cascade": False,
1551        "constraints": False,
1552        "purge": False,
1553    }
1554
1555
1556class Filter(Expression):
1557    arg_types = {"this": True, "expression": True}
1558
1559
1560class Check(Expression):
1561    pass
1562
1563
1564# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1565class Connect(Expression):
1566    arg_types = {"start": False, "connect": True}
1567
1568
1569class Prior(Expression):
1570    pass
1571
1572
1573class Directory(Expression):
1574    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1575    arg_types = {"this": True, "local": False, "row_format": False}
1576
1577
1578class ForeignKey(Expression):
1579    arg_types = {
1580        "expressions": True,
1581        "reference": False,
1582        "delete": False,
1583        "update": False,
1584    }
1585
1586
1587class ColumnPrefix(Expression):
1588    arg_types = {"this": True, "expression": True}
1589
1590
1591class PrimaryKey(Expression):
1592    arg_types = {"expressions": True, "options": False}
1593
1594
1595# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1596# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1597class Into(Expression):
1598    arg_types = {"this": True, "temporary": False, "unlogged": False}
1599
1600
1601class From(Expression):
1602    @property
1603    def name(self) -> str:
1604        return self.this.name
1605
1606    @property
1607    def alias_or_name(self) -> str:
1608        return self.this.alias_or_name
1609
1610
1611class Having(Expression):
1612    pass
1613
1614
1615class Hint(Expression):
1616    arg_types = {"expressions": True}
1617
1618
1619class JoinHint(Expression):
1620    arg_types = {"this": True, "expressions": True}
1621
1622
1623class Identifier(Expression):
1624    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1625
1626    @property
1627    def quoted(self) -> bool:
1628        return bool(self.args.get("quoted"))
1629
1630    @property
1631    def hashable_args(self) -> t.Any:
1632        return (self.this, self.quoted)
1633
1634    @property
1635    def output_name(self) -> str:
1636        return self.name
1637
1638
1639# https://www.postgresql.org/docs/current/indexes-opclass.html
1640class Opclass(Expression):
1641    arg_types = {"this": True, "expression": True}
1642
1643
1644class Index(Expression):
1645    arg_types = {
1646        "this": False,
1647        "table": False,
1648        "using": False,
1649        "where": False,
1650        "columns": False,
1651        "unique": False,
1652        "primary": False,
1653        "amp": False,  # teradata
1654        "partition_by": False,  # teradata
1655        "where": False,  # postgres partial indexes
1656    }
1657
1658
1659class Insert(DDL):
1660    arg_types = {
1661        "with": False,
1662        "this": True,
1663        "expression": False,
1664        "conflict": False,
1665        "returning": False,
1666        "overwrite": False,
1667        "exists": False,
1668        "partition": False,
1669        "alternative": False,
1670        "where": False,
1671        "ignore": False,
1672        "by_name": False,
1673    }
1674
1675    def with_(
1676        self,
1677        alias: ExpOrStr,
1678        as_: ExpOrStr,
1679        recursive: t.Optional[bool] = None,
1680        append: bool = True,
1681        dialect: DialectType = None,
1682        copy: bool = True,
1683        **opts,
1684    ) -> Insert:
1685        """
1686        Append to or set the common table expressions.
1687
1688        Example:
1689            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1690            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1691
1692        Args:
1693            alias: the SQL code string to parse as the table name.
1694                If an `Expression` instance is passed, this is used as-is.
1695            as_: the SQL code string to parse as the table expression.
1696                If an `Expression` instance is passed, it will be used as-is.
1697            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1698            append: if `True`, add to any existing expressions.
1699                Otherwise, this resets the expressions.
1700            dialect: the dialect used to parse the input expression.
1701            copy: if `False`, modify this expression instance in-place.
1702            opts: other options to use to parse the input expressions.
1703
1704        Returns:
1705            The modified expression.
1706        """
1707        return _apply_cte_builder(
1708            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1709        )
1710
1711
1712class OnConflict(Expression):
1713    arg_types = {
1714        "duplicate": False,
1715        "expressions": False,
1716        "nothing": False,
1717        "key": False,
1718        "constraint": False,
1719    }
1720
1721
1722class Returning(Expression):
1723    arg_types = {"expressions": True, "into": False}
1724
1725
1726# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1727class Introducer(Expression):
1728    arg_types = {"this": True, "expression": True}
1729
1730
1731# national char, like n'utf8'
1732class National(Expression):
1733    pass
1734
1735
1736class LoadData(Expression):
1737    arg_types = {
1738        "this": True,
1739        "local": False,
1740        "overwrite": False,
1741        "inpath": True,
1742        "partition": False,
1743        "input_format": False,
1744        "serde": False,
1745    }
1746
1747
1748class Partition(Expression):
1749    arg_types = {"expressions": True}
1750
1751
1752class Fetch(Expression):
1753    arg_types = {
1754        "direction": False,
1755        "count": False,
1756        "percent": False,
1757        "with_ties": False,
1758    }
1759
1760
1761class Group(Expression):
1762    arg_types = {
1763        "expressions": False,
1764        "grouping_sets": False,
1765        "cube": False,
1766        "rollup": False,
1767        "totals": False,
1768        "all": False,
1769    }
1770
1771
1772class Lambda(Expression):
1773    arg_types = {"this": True, "expressions": True}
1774
1775
1776class Limit(Expression):
1777    arg_types = {"this": False, "expression": True, "offset": False}
1778
1779
1780class Literal(Condition):
1781    arg_types = {"this": True, "is_string": True}
1782
1783    @property
1784    def hashable_args(self) -> t.Any:
1785        return (self.this, self.args.get("is_string"))
1786
1787    @classmethod
1788    def number(cls, number) -> Literal:
1789        return cls(this=str(number), is_string=False)
1790
1791    @classmethod
1792    def string(cls, string) -> Literal:
1793        return cls(this=str(string), is_string=True)
1794
1795    @property
1796    def output_name(self) -> str:
1797        return self.name
1798
1799
1800class Join(Expression):
1801    arg_types = {
1802        "this": True,
1803        "on": False,
1804        "side": False,
1805        "kind": False,
1806        "using": False,
1807        "method": False,
1808        "global": False,
1809        "hint": False,
1810    }
1811
1812    @property
1813    def method(self) -> str:
1814        return self.text("method").upper()
1815
1816    @property
1817    def kind(self) -> str:
1818        return self.text("kind").upper()
1819
1820    @property
1821    def side(self) -> str:
1822        return self.text("side").upper()
1823
1824    @property
1825    def hint(self) -> str:
1826        return self.text("hint").upper()
1827
1828    @property
1829    def alias_or_name(self) -> str:
1830        return self.this.alias_or_name
1831
1832    def on(
1833        self,
1834        *expressions: t.Optional[ExpOrStr],
1835        append: bool = True,
1836        dialect: DialectType = None,
1837        copy: bool = True,
1838        **opts,
1839    ) -> Join:
1840        """
1841        Append to or set the ON expressions.
1842
1843        Example:
1844            >>> import sqlglot
1845            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1846            'JOIN x ON y = 1'
1847
1848        Args:
1849            *expressions: the SQL code strings to parse.
1850                If an `Expression` instance is passed, it will be used as-is.
1851                Multiple expressions are combined with an AND operator.
1852            append: if `True`, AND the new expressions to any existing expression.
1853                Otherwise, this resets the expression.
1854            dialect: the dialect used to parse the input expressions.
1855            copy: if `False`, modify this expression instance in-place.
1856            opts: other options to use to parse the input expressions.
1857
1858        Returns:
1859            The modified Join expression.
1860        """
1861        join = _apply_conjunction_builder(
1862            *expressions,
1863            instance=self,
1864            arg="on",
1865            append=append,
1866            dialect=dialect,
1867            copy=copy,
1868            **opts,
1869        )
1870
1871        if join.kind == "CROSS":
1872            join.set("kind", None)
1873
1874        return join
1875
1876    def using(
1877        self,
1878        *expressions: t.Optional[ExpOrStr],
1879        append: bool = True,
1880        dialect: DialectType = None,
1881        copy: bool = True,
1882        **opts,
1883    ) -> Join:
1884        """
1885        Append to or set the USING expressions.
1886
1887        Example:
1888            >>> import sqlglot
1889            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1890            'JOIN x USING (foo, bla)'
1891
1892        Args:
1893            *expressions: the SQL code strings to parse.
1894                If an `Expression` instance is passed, it will be used as-is.
1895            append: if `True`, concatenate the new expressions to the existing "using" list.
1896                Otherwise, this resets the expression.
1897            dialect: the dialect used to parse the input expressions.
1898            copy: if `False`, modify this expression instance in-place.
1899            opts: other options to use to parse the input expressions.
1900
1901        Returns:
1902            The modified Join expression.
1903        """
1904        join = _apply_list_builder(
1905            *expressions,
1906            instance=self,
1907            arg="using",
1908            append=append,
1909            dialect=dialect,
1910            copy=copy,
1911            **opts,
1912        )
1913
1914        if join.kind == "CROSS":
1915            join.set("kind", None)
1916
1917        return join
1918
1919
1920class Lateral(UDTF):
1921    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1922
1923
1924class MatchRecognize(Expression):
1925    arg_types = {
1926        "partition_by": False,
1927        "order": False,
1928        "measures": False,
1929        "rows": False,
1930        "after": False,
1931        "pattern": False,
1932        "define": False,
1933        "alias": False,
1934    }
1935
1936
1937# Clickhouse FROM FINAL modifier
1938# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1939class Final(Expression):
1940    pass
1941
1942
1943class Offset(Expression):
1944    arg_types = {"this": False, "expression": True}
1945
1946
1947class Order(Expression):
1948    arg_types = {"this": False, "expressions": True}
1949
1950
1951# hive specific sorts
1952# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1953class Cluster(Order):
1954    pass
1955
1956
1957class Distribute(Order):
1958    pass
1959
1960
1961class Sort(Order):
1962    pass
1963
1964
1965class Ordered(Expression):
1966    arg_types = {"this": True, "desc": False, "nulls_first": True}
1967
1968
1969class Property(Expression):
1970    arg_types = {"this": True, "value": True}
1971
1972
1973class AlgorithmProperty(Property):
1974    arg_types = {"this": True}
1975
1976
1977class AutoIncrementProperty(Property):
1978    arg_types = {"this": True}
1979
1980
1981class BlockCompressionProperty(Property):
1982    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1983
1984
1985class CharacterSetProperty(Property):
1986    arg_types = {"this": True, "default": True}
1987
1988
1989class ChecksumProperty(Property):
1990    arg_types = {"on": False, "default": False}
1991
1992
1993class CollateProperty(Property):
1994    arg_types = {"this": True, "default": False}
1995
1996
1997class CopyGrantsProperty(Property):
1998    arg_types = {}
1999
2000
2001class DataBlocksizeProperty(Property):
2002    arg_types = {
2003        "size": False,
2004        "units": False,
2005        "minimum": False,
2006        "maximum": False,
2007        "default": False,
2008    }
2009
2010
2011class DefinerProperty(Property):
2012    arg_types = {"this": True}
2013
2014
2015class DistKeyProperty(Property):
2016    arg_types = {"this": True}
2017
2018
2019class DistStyleProperty(Property):
2020    arg_types = {"this": True}
2021
2022
2023class EngineProperty(Property):
2024    arg_types = {"this": True}
2025
2026
2027class HeapProperty(Property):
2028    arg_types = {}
2029
2030
2031class ToTableProperty(Property):
2032    arg_types = {"this": True}
2033
2034
2035class ExecuteAsProperty(Property):
2036    arg_types = {"this": True}
2037
2038
2039class ExternalProperty(Property):
2040    arg_types = {"this": False}
2041
2042
2043class FallbackProperty(Property):
2044    arg_types = {"no": True, "protection": False}
2045
2046
2047class FileFormatProperty(Property):
2048    arg_types = {"this": True}
2049
2050
2051class FreespaceProperty(Property):
2052    arg_types = {"this": True, "percent": False}
2053
2054
2055class InputModelProperty(Property):
2056    arg_types = {"this": True}
2057
2058
2059class OutputModelProperty(Property):
2060    arg_types = {"this": True}
2061
2062
2063class IsolatedLoadingProperty(Property):
2064    arg_types = {
2065        "no": True,
2066        "concurrent": True,
2067        "for_all": True,
2068        "for_insert": True,
2069        "for_none": True,
2070    }
2071
2072
2073class JournalProperty(Property):
2074    arg_types = {
2075        "no": False,
2076        "dual": False,
2077        "before": False,
2078        "local": False,
2079        "after": False,
2080    }
2081
2082
2083class LanguageProperty(Property):
2084    arg_types = {"this": True}
2085
2086
2087# spark ddl
2088class ClusteredByProperty(Property):
2089    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2090
2091
2092class DictProperty(Property):
2093    arg_types = {"this": True, "kind": True, "settings": False}
2094
2095
2096class DictSubProperty(Property):
2097    pass
2098
2099
2100class DictRange(Property):
2101    arg_types = {"this": True, "min": True, "max": True}
2102
2103
2104# Clickhouse CREATE ... ON CLUSTER modifier
2105# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2106class OnCluster(Property):
2107    arg_types = {"this": True}
2108
2109
2110class LikeProperty(Property):
2111    arg_types = {"this": True, "expressions": False}
2112
2113
2114class LocationProperty(Property):
2115    arg_types = {"this": True}
2116
2117
2118class LockingProperty(Property):
2119    arg_types = {
2120        "this": False,
2121        "kind": True,
2122        "for_or_in": False,
2123        "lock_type": True,
2124        "override": False,
2125    }
2126
2127
2128class LogProperty(Property):
2129    arg_types = {"no": True}
2130
2131
2132class MaterializedProperty(Property):
2133    arg_types = {"this": False}
2134
2135
2136class MergeBlockRatioProperty(Property):
2137    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2138
2139
2140class NoPrimaryIndexProperty(Property):
2141    arg_types = {}
2142
2143
2144class OnProperty(Property):
2145    arg_types = {"this": True}
2146
2147
2148class OnCommitProperty(Property):
2149    arg_types = {"delete": False}
2150
2151
2152class PartitionedByProperty(Property):
2153    arg_types = {"this": True}
2154
2155
2156# https://www.postgresql.org/docs/current/sql-createtable.html
2157class PartitionBoundSpec(Expression):
2158    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2159    arg_types = {
2160        "this": False,
2161        "expression": False,
2162        "from_expressions": False,
2163        "to_expressions": False,
2164    }
2165
2166
2167class PartitionedOfProperty(Property):
2168    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2169    arg_types = {"this": True, "expression": True}
2170
2171
2172class RemoteWithConnectionModelProperty(Property):
2173    arg_types = {"this": True}
2174
2175
2176class ReturnsProperty(Property):
2177    arg_types = {"this": True, "is_table": False, "table": False}
2178
2179
2180class RowFormatProperty(Property):
2181    arg_types = {"this": True}
2182
2183
2184class RowFormatDelimitedProperty(Property):
2185    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2186    arg_types = {
2187        "fields": False,
2188        "escaped": False,
2189        "collection_items": False,
2190        "map_keys": False,
2191        "lines": False,
2192        "null": False,
2193        "serde": False,
2194    }
2195
2196
2197class RowFormatSerdeProperty(Property):
2198    arg_types = {"this": True, "serde_properties": False}
2199
2200
2201# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2202class QueryTransform(Expression):
2203    arg_types = {
2204        "expressions": True,
2205        "command_script": True,
2206        "schema": False,
2207        "row_format_before": False,
2208        "record_writer": False,
2209        "row_format_after": False,
2210        "record_reader": False,
2211    }
2212
2213
2214class SampleProperty(Property):
2215    arg_types = {"this": True}
2216
2217
2218class SchemaCommentProperty(Property):
2219    arg_types = {"this": True}
2220
2221
2222class SerdeProperties(Property):
2223    arg_types = {"expressions": True}
2224
2225
2226class SetProperty(Property):
2227    arg_types = {"multi": True}
2228
2229
2230class SettingsProperty(Property):
2231    arg_types = {"expressions": True}
2232
2233
2234class SortKeyProperty(Property):
2235    arg_types = {"this": True, "compound": False}
2236
2237
2238class SqlSecurityProperty(Property):
2239    arg_types = {"definer": True}
2240
2241
2242class StabilityProperty(Property):
2243    arg_types = {"this": True}
2244
2245
2246class TemporaryProperty(Property):
2247    arg_types = {}
2248
2249
2250class TransformModelProperty(Property):
2251    arg_types = {"expressions": True}
2252
2253
2254class TransientProperty(Property):
2255    arg_types = {"this": False}
2256
2257
2258class VolatileProperty(Property):
2259    arg_types = {"this": False}
2260
2261
2262class WithDataProperty(Property):
2263    arg_types = {"no": True, "statistics": False}
2264
2265
2266class WithJournalTableProperty(Property):
2267    arg_types = {"this": True}
2268
2269
2270class WithSystemVersioningProperty(Property):
2271    # this -> history table name, expression -> data consistency check
2272    arg_types = {"this": False, "expression": False}
2273
2274
2275class Properties(Expression):
2276    arg_types = {"expressions": True}
2277
2278    NAME_TO_PROPERTY = {
2279        "ALGORITHM": AlgorithmProperty,
2280        "AUTO_INCREMENT": AutoIncrementProperty,
2281        "CHARACTER SET": CharacterSetProperty,
2282        "CLUSTERED_BY": ClusteredByProperty,
2283        "COLLATE": CollateProperty,
2284        "COMMENT": SchemaCommentProperty,
2285        "DEFINER": DefinerProperty,
2286        "DISTKEY": DistKeyProperty,
2287        "DISTSTYLE": DistStyleProperty,
2288        "ENGINE": EngineProperty,
2289        "EXECUTE AS": ExecuteAsProperty,
2290        "FORMAT": FileFormatProperty,
2291        "LANGUAGE": LanguageProperty,
2292        "LOCATION": LocationProperty,
2293        "PARTITIONED_BY": PartitionedByProperty,
2294        "RETURNS": ReturnsProperty,
2295        "ROW_FORMAT": RowFormatProperty,
2296        "SORTKEY": SortKeyProperty,
2297    }
2298
2299    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2300
2301    # CREATE property locations
2302    # Form: schema specified
2303    #   create [POST_CREATE]
2304    #     table a [POST_NAME]
2305    #     (b int) [POST_SCHEMA]
2306    #     with ([POST_WITH])
2307    #     index (b) [POST_INDEX]
2308    #
2309    # Form: alias selection
2310    #   create [POST_CREATE]
2311    #     table a [POST_NAME]
2312    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2313    #     index (c) [POST_INDEX]
2314    class Location(AutoName):
2315        POST_CREATE = auto()
2316        POST_NAME = auto()
2317        POST_SCHEMA = auto()
2318        POST_WITH = auto()
2319        POST_ALIAS = auto()
2320        POST_EXPRESSION = auto()
2321        POST_INDEX = auto()
2322        UNSUPPORTED = auto()
2323
2324    @classmethod
2325    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2326        expressions = []
2327        for key, value in properties_dict.items():
2328            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2329            if property_cls:
2330                expressions.append(property_cls(this=convert(value)))
2331            else:
2332                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2333
2334        return cls(expressions=expressions)
2335
2336
2337class Qualify(Expression):
2338    pass
2339
2340
2341class InputOutputFormat(Expression):
2342    arg_types = {"input_format": False, "output_format": False}
2343
2344
2345# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2346class Return(Expression):
2347    pass
2348
2349
2350class Reference(Expression):
2351    arg_types = {"this": True, "expressions": False, "options": False}
2352
2353
2354class Tuple(Expression):
2355    arg_types = {"expressions": False}
2356
2357    def isin(
2358        self,
2359        *expressions: t.Any,
2360        query: t.Optional[ExpOrStr] = None,
2361        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2362        copy: bool = True,
2363        **opts,
2364    ) -> In:
2365        return In(
2366            this=maybe_copy(self, copy),
2367            expressions=[convert(e, copy=copy) for e in expressions],
2368            query=maybe_parse(query, copy=copy, **opts) if query else None,
2369            unnest=Unnest(
2370                expressions=[
2371                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2372                ]
2373            )
2374            if unnest
2375            else None,
2376        )
2377
2378
2379class Subqueryable(Unionable):
2380    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2381        """
2382        Convert this expression to an aliased expression that can be used as a Subquery.
2383
2384        Example:
2385            >>> subquery = Select().select("x").from_("tbl").subquery()
2386            >>> Select().select("x").from_(subquery).sql()
2387            'SELECT x FROM (SELECT x FROM tbl)'
2388
2389        Args:
2390            alias (str | Identifier): an optional alias for the subquery
2391            copy (bool): if `False`, modify this expression instance in-place.
2392
2393        Returns:
2394            Alias: the subquery
2395        """
2396        instance = maybe_copy(self, copy)
2397        if not isinstance(alias, Expression):
2398            alias = TableAlias(this=to_identifier(alias)) if alias else None
2399
2400        return Subquery(this=instance, alias=alias)
2401
2402    def limit(
2403        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2404    ) -> Select:
2405        raise NotImplementedError
2406
2407    @property
2408    def ctes(self):
2409        with_ = self.args.get("with")
2410        if not with_:
2411            return []
2412        return with_.expressions
2413
2414    @property
2415    def selects(self) -> t.List[Expression]:
2416        raise NotImplementedError("Subqueryable objects must implement `selects`")
2417
2418    @property
2419    def named_selects(self) -> t.List[str]:
2420        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2421
2422    def select(
2423        self,
2424        *expressions: t.Optional[ExpOrStr],
2425        append: bool = True,
2426        dialect: DialectType = None,
2427        copy: bool = True,
2428        **opts,
2429    ) -> Subqueryable:
2430        raise NotImplementedError("Subqueryable objects must implement `select`")
2431
2432    def with_(
2433        self,
2434        alias: ExpOrStr,
2435        as_: ExpOrStr,
2436        recursive: t.Optional[bool] = None,
2437        append: bool = True,
2438        dialect: DialectType = None,
2439        copy: bool = True,
2440        **opts,
2441    ) -> Subqueryable:
2442        """
2443        Append to or set the common table expressions.
2444
2445        Example:
2446            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2447            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2448
2449        Args:
2450            alias: the SQL code string to parse as the table name.
2451                If an `Expression` instance is passed, this is used as-is.
2452            as_: the SQL code string to parse as the table expression.
2453                If an `Expression` instance is passed, it will be used as-is.
2454            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2455            append: if `True`, add to any existing expressions.
2456                Otherwise, this resets the expressions.
2457            dialect: the dialect used to parse the input expression.
2458            copy: if `False`, modify this expression instance in-place.
2459            opts: other options to use to parse the input expressions.
2460
2461        Returns:
2462            The modified expression.
2463        """
2464        return _apply_cte_builder(
2465            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2466        )
2467
2468
2469QUERY_MODIFIERS = {
2470    "match": False,
2471    "laterals": False,
2472    "joins": False,
2473    "connect": False,
2474    "pivots": False,
2475    "where": False,
2476    "group": False,
2477    "having": False,
2478    "qualify": False,
2479    "windows": False,
2480    "distribute": False,
2481    "sort": False,
2482    "cluster": False,
2483    "order": False,
2484    "limit": False,
2485    "offset": False,
2486    "locks": False,
2487    "sample": False,
2488    "settings": False,
2489    "format": False,
2490}
2491
2492
2493# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2494class WithTableHint(Expression):
2495    arg_types = {"expressions": True}
2496
2497
2498# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2499class IndexTableHint(Expression):
2500    arg_types = {"this": True, "expressions": False, "target": False}
2501
2502
2503class Table(Expression):
2504    arg_types = {
2505        "this": True,
2506        "alias": False,
2507        "db": False,
2508        "catalog": False,
2509        "laterals": False,
2510        "joins": False,
2511        "pivots": False,
2512        "hints": False,
2513        "system_time": False,
2514        "version": False,
2515        "format": False,
2516        "pattern": False,
2517        "index": False,
2518        "ordinality": False,
2519    }
2520
2521    @property
2522    def name(self) -> str:
2523        if isinstance(self.this, Func):
2524            return ""
2525        return self.this.name
2526
2527    @property
2528    def db(self) -> str:
2529        return self.text("db")
2530
2531    @property
2532    def catalog(self) -> str:
2533        return self.text("catalog")
2534
2535    @property
2536    def selects(self) -> t.List[Expression]:
2537        return []
2538
2539    @property
2540    def named_selects(self) -> t.List[str]:
2541        return []
2542
2543    @property
2544    def parts(self) -> t.List[Expression]:
2545        """Return the parts of a table in order catalog, db, table."""
2546        parts: t.List[Expression] = []
2547
2548        for arg in ("catalog", "db", "this"):
2549            part = self.args.get(arg)
2550
2551            if isinstance(part, Dot):
2552                parts.extend(part.flatten())
2553            elif isinstance(part, Expression):
2554                parts.append(part)
2555
2556        return parts
2557
2558
2559class Union(Subqueryable):
2560    arg_types = {
2561        "with": False,
2562        "this": True,
2563        "expression": True,
2564        "distinct": False,
2565        "by_name": False,
2566        **QUERY_MODIFIERS,
2567    }
2568
2569    def limit(
2570        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2571    ) -> Select:
2572        """
2573        Set the LIMIT expression.
2574
2575        Example:
2576            >>> select("1").union(select("1")).limit(1).sql()
2577            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2578
2579        Args:
2580            expression: the SQL code string to parse.
2581                This can also be an integer.
2582                If a `Limit` instance is passed, this is used as-is.
2583                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2584            dialect: the dialect used to parse the input expression.
2585            copy: if `False`, modify this expression instance in-place.
2586            opts: other options to use to parse the input expressions.
2587
2588        Returns:
2589            The limited subqueryable.
2590        """
2591        return (
2592            select("*")
2593            .from_(self.subquery(alias="_l_0", copy=copy))
2594            .limit(expression, dialect=dialect, copy=False, **opts)
2595        )
2596
2597    def select(
2598        self,
2599        *expressions: t.Optional[ExpOrStr],
2600        append: bool = True,
2601        dialect: DialectType = None,
2602        copy: bool = True,
2603        **opts,
2604    ) -> Union:
2605        """Append to or set the SELECT of the union recursively.
2606
2607        Example:
2608            >>> from sqlglot import parse_one
2609            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2610            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2611
2612        Args:
2613            *expressions: the SQL code strings to parse.
2614                If an `Expression` instance is passed, it will be used as-is.
2615            append: if `True`, add to any existing expressions.
2616                Otherwise, this resets the expressions.
2617            dialect: the dialect used to parse the input expressions.
2618            copy: if `False`, modify this expression instance in-place.
2619            opts: other options to use to parse the input expressions.
2620
2621        Returns:
2622            Union: the modified expression.
2623        """
2624        this = self.copy() if copy else self
2625        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2626        this.expression.unnest().select(
2627            *expressions, append=append, dialect=dialect, copy=False, **opts
2628        )
2629        return this
2630
2631    @property
2632    def named_selects(self) -> t.List[str]:
2633        return self.this.unnest().named_selects
2634
2635    @property
2636    def is_star(self) -> bool:
2637        return self.this.is_star or self.expression.is_star
2638
2639    @property
2640    def selects(self) -> t.List[Expression]:
2641        return self.this.unnest().selects
2642
2643    @property
2644    def left(self) -> Expression:
2645        return self.this
2646
2647    @property
2648    def right(self) -> Expression:
2649        return self.expression
2650
2651
2652class Except(Union):
2653    pass
2654
2655
2656class Intersect(Union):
2657    pass
2658
2659
2660class Unnest(UDTF):
2661    arg_types = {
2662        "expressions": True,
2663        "alias": False,
2664        "offset": False,
2665    }
2666
2667
2668class Update(Expression):
2669    arg_types = {
2670        "with": False,
2671        "this": False,
2672        "expressions": True,
2673        "from": False,
2674        "where": False,
2675        "returning": False,
2676        "order": False,
2677        "limit": False,
2678    }
2679
2680
2681class Values(UDTF):
2682    arg_types = {"expressions": True, "alias": False}
2683
2684
2685class Var(Expression):
2686    pass
2687
2688
2689class Version(Expression):
2690    """
2691    Time travel, iceberg, bigquery etc
2692    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2693    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2694    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2695    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2696    this is either TIMESTAMP or VERSION
2697    kind is ("AS OF", "BETWEEN")
2698    """
2699
2700    arg_types = {"this": True, "kind": True, "expression": False}
2701
2702
2703class Schema(Expression):
2704    arg_types = {"this": False, "expressions": False}
2705
2706
2707# https://dev.mysql.com/doc/refman/8.0/en/select.html
2708# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2709class Lock(Expression):
2710    arg_types = {"update": True, "expressions": False, "wait": False}
2711
2712
2713class Select(Subqueryable):
2714    arg_types = {
2715        "with": False,
2716        "kind": False,
2717        "expressions": False,
2718        "hint": False,
2719        "distinct": False,
2720        "into": False,
2721        "from": False,
2722        **QUERY_MODIFIERS,
2723    }
2724
2725    def from_(
2726        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2727    ) -> Select:
2728        """
2729        Set the FROM expression.
2730
2731        Example:
2732            >>> Select().from_("tbl").select("x").sql()
2733            'SELECT x FROM tbl'
2734
2735        Args:
2736            expression : the SQL code strings to parse.
2737                If a `From` instance is passed, this is used as-is.
2738                If another `Expression` instance is passed, it will be wrapped in a `From`.
2739            dialect: the dialect used to parse the input expression.
2740            copy: if `False`, modify this expression instance in-place.
2741            opts: other options to use to parse the input expressions.
2742
2743        Returns:
2744            The modified Select expression.
2745        """
2746        return _apply_builder(
2747            expression=expression,
2748            instance=self,
2749            arg="from",
2750            into=From,
2751            prefix="FROM",
2752            dialect=dialect,
2753            copy=copy,
2754            **opts,
2755        )
2756
2757    def group_by(
2758        self,
2759        *expressions: t.Optional[ExpOrStr],
2760        append: bool = True,
2761        dialect: DialectType = None,
2762        copy: bool = True,
2763        **opts,
2764    ) -> Select:
2765        """
2766        Set the GROUP BY expression.
2767
2768        Example:
2769            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2770            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2771
2772        Args:
2773            *expressions: the SQL code strings to parse.
2774                If a `Group` instance is passed, this is used as-is.
2775                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2776                If nothing is passed in then a group by is not applied to the expression
2777            append: if `True`, add to any existing expressions.
2778                Otherwise, this flattens all the `Group` expression into a single expression.
2779            dialect: the dialect used to parse the input expression.
2780            copy: if `False`, modify this expression instance in-place.
2781            opts: other options to use to parse the input expressions.
2782
2783        Returns:
2784            The modified Select expression.
2785        """
2786        if not expressions:
2787            return self if not copy else self.copy()
2788
2789        return _apply_child_list_builder(
2790            *expressions,
2791            instance=self,
2792            arg="group",
2793            append=append,
2794            copy=copy,
2795            prefix="GROUP BY",
2796            into=Group,
2797            dialect=dialect,
2798            **opts,
2799        )
2800
2801    def order_by(
2802        self,
2803        *expressions: t.Optional[ExpOrStr],
2804        append: bool = True,
2805        dialect: DialectType = None,
2806        copy: bool = True,
2807        **opts,
2808    ) -> Select:
2809        """
2810        Set the ORDER BY expression.
2811
2812        Example:
2813            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2814            'SELECT x FROM tbl ORDER BY x DESC'
2815
2816        Args:
2817            *expressions: the SQL code strings to parse.
2818                If a `Group` instance is passed, this is used as-is.
2819                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2820            append: if `True`, add to any existing expressions.
2821                Otherwise, this flattens all the `Order` expression into a single expression.
2822            dialect: the dialect used to parse the input expression.
2823            copy: if `False`, modify this expression instance in-place.
2824            opts: other options to use to parse the input expressions.
2825
2826        Returns:
2827            The modified Select expression.
2828        """
2829        return _apply_child_list_builder(
2830            *expressions,
2831            instance=self,
2832            arg="order",
2833            append=append,
2834            copy=copy,
2835            prefix="ORDER BY",
2836            into=Order,
2837            dialect=dialect,
2838            **opts,
2839        )
2840
2841    def sort_by(
2842        self,
2843        *expressions: t.Optional[ExpOrStr],
2844        append: bool = True,
2845        dialect: DialectType = None,
2846        copy: bool = True,
2847        **opts,
2848    ) -> Select:
2849        """
2850        Set the SORT BY expression.
2851
2852        Example:
2853            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2854            'SELECT x FROM tbl SORT BY x DESC'
2855
2856        Args:
2857            *expressions: the SQL code strings to parse.
2858                If a `Group` instance is passed, this is used as-is.
2859                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2860            append: if `True`, add to any existing expressions.
2861                Otherwise, this flattens all the `Order` expression into a single expression.
2862            dialect: the dialect used to parse the input expression.
2863            copy: if `False`, modify this expression instance in-place.
2864            opts: other options to use to parse the input expressions.
2865
2866        Returns:
2867            The modified Select expression.
2868        """
2869        return _apply_child_list_builder(
2870            *expressions,
2871            instance=self,
2872            arg="sort",
2873            append=append,
2874            copy=copy,
2875            prefix="SORT BY",
2876            into=Sort,
2877            dialect=dialect,
2878            **opts,
2879        )
2880
2881    def cluster_by(
2882        self,
2883        *expressions: t.Optional[ExpOrStr],
2884        append: bool = True,
2885        dialect: DialectType = None,
2886        copy: bool = True,
2887        **opts,
2888    ) -> Select:
2889        """
2890        Set the CLUSTER BY expression.
2891
2892        Example:
2893            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2894            'SELECT x FROM tbl CLUSTER BY x DESC'
2895
2896        Args:
2897            *expressions: the SQL code strings to parse.
2898                If a `Group` instance is passed, this is used as-is.
2899                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2900            append: if `True`, add to any existing expressions.
2901                Otherwise, this flattens all the `Order` expression into a single expression.
2902            dialect: the dialect used to parse the input expression.
2903            copy: if `False`, modify this expression instance in-place.
2904            opts: other options to use to parse the input expressions.
2905
2906        Returns:
2907            The modified Select expression.
2908        """
2909        return _apply_child_list_builder(
2910            *expressions,
2911            instance=self,
2912            arg="cluster",
2913            append=append,
2914            copy=copy,
2915            prefix="CLUSTER BY",
2916            into=Cluster,
2917            dialect=dialect,
2918            **opts,
2919        )
2920
2921    def limit(
2922        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2923    ) -> Select:
2924        """
2925        Set the LIMIT expression.
2926
2927        Example:
2928            >>> Select().from_("tbl").select("x").limit(10).sql()
2929            'SELECT x FROM tbl LIMIT 10'
2930
2931        Args:
2932            expression: the SQL code string to parse.
2933                This can also be an integer.
2934                If a `Limit` instance is passed, this is used as-is.
2935                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2936            dialect: the dialect used to parse the input expression.
2937            copy: if `False`, modify this expression instance in-place.
2938            opts: other options to use to parse the input expressions.
2939
2940        Returns:
2941            Select: the modified expression.
2942        """
2943        return _apply_builder(
2944            expression=expression,
2945            instance=self,
2946            arg="limit",
2947            into=Limit,
2948            prefix="LIMIT",
2949            dialect=dialect,
2950            copy=copy,
2951            into_arg="expression",
2952            **opts,
2953        )
2954
2955    def offset(
2956        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2957    ) -> Select:
2958        """
2959        Set the OFFSET expression.
2960
2961        Example:
2962            >>> Select().from_("tbl").select("x").offset(10).sql()
2963            'SELECT x FROM tbl OFFSET 10'
2964
2965        Args:
2966            expression: the SQL code string to parse.
2967                This can also be an integer.
2968                If a `Offset` instance is passed, this is used as-is.
2969                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2970            dialect: the dialect used to parse the input expression.
2971            copy: if `False`, modify this expression instance in-place.
2972            opts: other options to use to parse the input expressions.
2973
2974        Returns:
2975            The modified Select expression.
2976        """
2977        return _apply_builder(
2978            expression=expression,
2979            instance=self,
2980            arg="offset",
2981            into=Offset,
2982            prefix="OFFSET",
2983            dialect=dialect,
2984            copy=copy,
2985            into_arg="expression",
2986            **opts,
2987        )
2988
2989    def select(
2990        self,
2991        *expressions: t.Optional[ExpOrStr],
2992        append: bool = True,
2993        dialect: DialectType = None,
2994        copy: bool = True,
2995        **opts,
2996    ) -> Select:
2997        """
2998        Append to or set the SELECT expressions.
2999
3000        Example:
3001            >>> Select().select("x", "y").sql()
3002            'SELECT x, y'
3003
3004        Args:
3005            *expressions: the SQL code strings to parse.
3006                If an `Expression` instance is passed, it will be used as-is.
3007            append: if `True`, add to any existing expressions.
3008                Otherwise, this resets the expressions.
3009            dialect: the dialect used to parse the input expressions.
3010            copy: if `False`, modify this expression instance in-place.
3011            opts: other options to use to parse the input expressions.
3012
3013        Returns:
3014            The modified Select expression.
3015        """
3016        return _apply_list_builder(
3017            *expressions,
3018            instance=self,
3019            arg="expressions",
3020            append=append,
3021            dialect=dialect,
3022            copy=copy,
3023            **opts,
3024        )
3025
3026    def lateral(
3027        self,
3028        *expressions: t.Optional[ExpOrStr],
3029        append: bool = True,
3030        dialect: DialectType = None,
3031        copy: bool = True,
3032        **opts,
3033    ) -> Select:
3034        """
3035        Append to or set the LATERAL expressions.
3036
3037        Example:
3038            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3039            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3040
3041        Args:
3042            *expressions: the SQL code strings to parse.
3043                If an `Expression` instance is passed, it will be used as-is.
3044            append: if `True`, add to any existing expressions.
3045                Otherwise, this resets the expressions.
3046            dialect: the dialect used to parse the input expressions.
3047            copy: if `False`, modify this expression instance in-place.
3048            opts: other options to use to parse the input expressions.
3049
3050        Returns:
3051            The modified Select expression.
3052        """
3053        return _apply_list_builder(
3054            *expressions,
3055            instance=self,
3056            arg="laterals",
3057            append=append,
3058            into=Lateral,
3059            prefix="LATERAL VIEW",
3060            dialect=dialect,
3061            copy=copy,
3062            **opts,
3063        )
3064
3065    def join(
3066        self,
3067        expression: ExpOrStr,
3068        on: t.Optional[ExpOrStr] = None,
3069        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3070        append: bool = True,
3071        join_type: t.Optional[str] = None,
3072        join_alias: t.Optional[Identifier | str] = None,
3073        dialect: DialectType = None,
3074        copy: bool = True,
3075        **opts,
3076    ) -> Select:
3077        """
3078        Append to or set the JOIN expressions.
3079
3080        Example:
3081            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3082            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3083
3084            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3085            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3086
3087            Use `join_type` to change the type of join:
3088
3089            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3090            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3091
3092        Args:
3093            expression: the SQL code string to parse.
3094                If an `Expression` instance is passed, it will be used as-is.
3095            on: optionally specify the join "on" criteria as a SQL string.
3096                If an `Expression` instance is passed, it will be used as-is.
3097            using: optionally specify the join "using" criteria as a SQL string.
3098                If an `Expression` instance is passed, it will be used as-is.
3099            append: if `True`, add to any existing expressions.
3100                Otherwise, this resets the expressions.
3101            join_type: if set, alter the parsed join type.
3102            join_alias: an optional alias for the joined source.
3103            dialect: the dialect used to parse the input expressions.
3104            copy: if `False`, modify this expression instance in-place.
3105            opts: other options to use to parse the input expressions.
3106
3107        Returns:
3108            Select: the modified expression.
3109        """
3110        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3111
3112        try:
3113            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3114        except ParseError:
3115            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3116
3117        join = expression if isinstance(expression, Join) else Join(this=expression)
3118
3119        if isinstance(join.this, Select):
3120            join.this.replace(join.this.subquery())
3121
3122        if join_type:
3123            method: t.Optional[Token]
3124            side: t.Optional[Token]
3125            kind: t.Optional[Token]
3126
3127            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3128
3129            if method:
3130                join.set("method", method.text)
3131            if side:
3132                join.set("side", side.text)
3133            if kind:
3134                join.set("kind", kind.text)
3135
3136        if on:
3137            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3138            join.set("on", on)
3139
3140        if using:
3141            join = _apply_list_builder(
3142                *ensure_list(using),
3143                instance=join,
3144                arg="using",
3145                append=append,
3146                copy=copy,
3147                into=Identifier,
3148                **opts,
3149            )
3150
3151        if join_alias:
3152            join.set("this", alias_(join.this, join_alias, table=True))
3153
3154        return _apply_list_builder(
3155            join,
3156            instance=self,
3157            arg="joins",
3158            append=append,
3159            copy=copy,
3160            **opts,
3161        )
3162
3163    def where(
3164        self,
3165        *expressions: t.Optional[ExpOrStr],
3166        append: bool = True,
3167        dialect: DialectType = None,
3168        copy: bool = True,
3169        **opts,
3170    ) -> Select:
3171        """
3172        Append to or set the WHERE expressions.
3173
3174        Example:
3175            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3176            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3177
3178        Args:
3179            *expressions: the SQL code strings to parse.
3180                If an `Expression` instance is passed, it will be used as-is.
3181                Multiple expressions are combined with an AND operator.
3182            append: if `True`, AND the new expressions to any existing expression.
3183                Otherwise, this resets the expression.
3184            dialect: the dialect used to parse the input expressions.
3185            copy: if `False`, modify this expression instance in-place.
3186            opts: other options to use to parse the input expressions.
3187
3188        Returns:
3189            Select: the modified expression.
3190        """
3191        return _apply_conjunction_builder(
3192            *expressions,
3193            instance=self,
3194            arg="where",
3195            append=append,
3196            into=Where,
3197            dialect=dialect,
3198            copy=copy,
3199            **opts,
3200        )
3201
3202    def having(
3203        self,
3204        *expressions: t.Optional[ExpOrStr],
3205        append: bool = True,
3206        dialect: DialectType = None,
3207        copy: bool = True,
3208        **opts,
3209    ) -> Select:
3210        """
3211        Append to or set the HAVING expressions.
3212
3213        Example:
3214            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3215            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3216
3217        Args:
3218            *expressions: the SQL code strings to parse.
3219                If an `Expression` instance is passed, it will be used as-is.
3220                Multiple expressions are combined with an AND operator.
3221            append: if `True`, AND the new expressions to any existing expression.
3222                Otherwise, this resets the expression.
3223            dialect: the dialect used to parse the input expressions.
3224            copy: if `False`, modify this expression instance in-place.
3225            opts: other options to use to parse the input expressions.
3226
3227        Returns:
3228            The modified Select expression.
3229        """
3230        return _apply_conjunction_builder(
3231            *expressions,
3232            instance=self,
3233            arg="having",
3234            append=append,
3235            into=Having,
3236            dialect=dialect,
3237            copy=copy,
3238            **opts,
3239        )
3240
3241    def window(
3242        self,
3243        *expressions: t.Optional[ExpOrStr],
3244        append: bool = True,
3245        dialect: DialectType = None,
3246        copy: bool = True,
3247        **opts,
3248    ) -> Select:
3249        return _apply_list_builder(
3250            *expressions,
3251            instance=self,
3252            arg="windows",
3253            append=append,
3254            into=Window,
3255            dialect=dialect,
3256            copy=copy,
3257            **opts,
3258        )
3259
3260    def qualify(
3261        self,
3262        *expressions: t.Optional[ExpOrStr],
3263        append: bool = True,
3264        dialect: DialectType = None,
3265        copy: bool = True,
3266        **opts,
3267    ) -> Select:
3268        return _apply_conjunction_builder(
3269            *expressions,
3270            instance=self,
3271            arg="qualify",
3272            append=append,
3273            into=Qualify,
3274            dialect=dialect,
3275            copy=copy,
3276            **opts,
3277        )
3278
3279    def distinct(
3280        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3281    ) -> Select:
3282        """
3283        Set the OFFSET expression.
3284
3285        Example:
3286            >>> Select().from_("tbl").select("x").distinct().sql()
3287            'SELECT DISTINCT x FROM tbl'
3288
3289        Args:
3290            ons: the expressions to distinct on
3291            distinct: whether the Select should be distinct
3292            copy: if `False`, modify this expression instance in-place.
3293
3294        Returns:
3295            Select: the modified expression.
3296        """
3297        instance = maybe_copy(self, copy)
3298        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3299        instance.set("distinct", Distinct(on=on) if distinct else None)
3300        return instance
3301
3302    def ctas(
3303        self,
3304        table: ExpOrStr,
3305        properties: t.Optional[t.Dict] = None,
3306        dialect: DialectType = None,
3307        copy: bool = True,
3308        **opts,
3309    ) -> Create:
3310        """
3311        Convert this expression to a CREATE TABLE AS statement.
3312
3313        Example:
3314            >>> Select().select("*").from_("tbl").ctas("x").sql()
3315            'CREATE TABLE x AS SELECT * FROM tbl'
3316
3317        Args:
3318            table: the SQL code string to parse as the table name.
3319                If another `Expression` instance is passed, it will be used as-is.
3320            properties: an optional mapping of table properties
3321            dialect: the dialect used to parse the input table.
3322            copy: if `False`, modify this expression instance in-place.
3323            opts: other options to use to parse the input table.
3324
3325        Returns:
3326            The new Create expression.
3327        """
3328        instance = maybe_copy(self, copy)
3329        table_expression = maybe_parse(
3330            table,
3331            into=Table,
3332            dialect=dialect,
3333            **opts,
3334        )
3335        properties_expression = None
3336        if properties:
3337            properties_expression = Properties.from_dict(properties)
3338
3339        return Create(
3340            this=table_expression,
3341            kind="table",
3342            expression=instance,
3343            properties=properties_expression,
3344        )
3345
3346    def lock(self, update: bool = True, copy: bool = True) -> Select:
3347        """
3348        Set the locking read mode for this expression.
3349
3350        Examples:
3351            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3352            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3353
3354            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3355            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3356
3357        Args:
3358            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3359            copy: if `False`, modify this expression instance in-place.
3360
3361        Returns:
3362            The modified expression.
3363        """
3364        inst = maybe_copy(self, copy)
3365        inst.set("locks", [Lock(update=update)])
3366
3367        return inst
3368
3369    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3370        """
3371        Set hints for this expression.
3372
3373        Examples:
3374            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3375            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3376
3377        Args:
3378            hints: The SQL code strings to parse as the hints.
3379                If an `Expression` instance is passed, it will be used as-is.
3380            dialect: The dialect used to parse the hints.
3381            copy: If `False`, modify this expression instance in-place.
3382
3383        Returns:
3384            The modified expression.
3385        """
3386        inst = maybe_copy(self, copy)
3387        inst.set(
3388            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3389        )
3390
3391        return inst
3392
3393    @property
3394    def named_selects(self) -> t.List[str]:
3395        return [e.output_name for e in self.expressions if e.alias_or_name]
3396
3397    @property
3398    def is_star(self) -> bool:
3399        return any(expression.is_star for expression in self.expressions)
3400
3401    @property
3402    def selects(self) -> t.List[Expression]:
3403        return self.expressions
3404
3405
3406class Subquery(DerivedTable, Unionable):
3407    arg_types = {
3408        "this": True,
3409        "alias": False,
3410        "with": False,
3411        **QUERY_MODIFIERS,
3412    }
3413
3414    def unnest(self):
3415        """
3416        Returns the first non subquery.
3417        """
3418        expression = self
3419        while isinstance(expression, Subquery):
3420            expression = expression.this
3421        return expression
3422
3423    def unwrap(self) -> Subquery:
3424        expression = self
3425        while expression.same_parent and expression.is_wrapper:
3426            expression = t.cast(Subquery, expression.parent)
3427        return expression
3428
3429    @property
3430    def is_wrapper(self) -> bool:
3431        """
3432        Whether this Subquery acts as a simple wrapper around another expression.
3433
3434        SELECT * FROM (((SELECT * FROM t)))
3435                      ^
3436                      This corresponds to a "wrapper" Subquery node
3437        """
3438        return all(v is None for k, v in self.args.items() if k != "this")
3439
3440    @property
3441    def is_star(self) -> bool:
3442        return self.this.is_star
3443
3444    @property
3445    def output_name(self) -> str:
3446        return self.alias
3447
3448
3449class TableSample(Expression):
3450    arg_types = {
3451        "this": False,
3452        "expressions": False,
3453        "method": False,
3454        "bucket_numerator": False,
3455        "bucket_denominator": False,
3456        "bucket_field": False,
3457        "percent": False,
3458        "rows": False,
3459        "size": False,
3460        "seed": False,
3461        "kind": False,
3462    }
3463
3464
3465class Tag(Expression):
3466    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3467
3468    arg_types = {
3469        "this": False,
3470        "prefix": False,
3471        "postfix": False,
3472    }
3473
3474
3475# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3476# https://duckdb.org/docs/sql/statements/pivot
3477class Pivot(Expression):
3478    arg_types = {
3479        "this": False,
3480        "alias": False,
3481        "expressions": False,
3482        "field": False,
3483        "unpivot": False,
3484        "using": False,
3485        "group": False,
3486        "columns": False,
3487        "include_nulls": False,
3488    }
3489
3490
3491class Window(Condition):
3492    arg_types = {
3493        "this": True,
3494        "partition_by": False,
3495        "order": False,
3496        "spec": False,
3497        "alias": False,
3498        "over": False,
3499        "first": False,
3500    }
3501
3502
3503class WindowSpec(Expression):
3504    arg_types = {
3505        "kind": False,
3506        "start": False,
3507        "start_side": False,
3508        "end": False,
3509        "end_side": False,
3510    }
3511
3512
3513class Where(Expression):
3514    pass
3515
3516
3517class Star(Expression):
3518    arg_types = {"except": False, "replace": False}
3519
3520    @property
3521    def name(self) -> str:
3522        return "*"
3523
3524    @property
3525    def output_name(self) -> str:
3526        return self.name
3527
3528
3529class Parameter(Condition):
3530    arg_types = {"this": True, "expression": False}
3531
3532
3533class SessionParameter(Condition):
3534    arg_types = {"this": True, "kind": False}
3535
3536
3537class Placeholder(Condition):
3538    arg_types = {"this": False, "kind": False}
3539
3540
3541class Null(Condition):
3542    arg_types: t.Dict[str, t.Any] = {}
3543
3544    @property
3545    def name(self) -> str:
3546        return "NULL"
3547
3548
3549class Boolean(Condition):
3550    pass
3551
3552
3553class DataTypeParam(Expression):
3554    arg_types = {"this": True, "expression": False}
3555
3556
3557class DataType(Expression):
3558    arg_types = {
3559        "this": True,
3560        "expressions": False,
3561        "nested": False,
3562        "values": False,
3563        "prefix": False,
3564        "kind": False,
3565    }
3566
3567    class Type(AutoName):
3568        ARRAY = auto()
3569        BIGDECIMAL = auto()
3570        BIGINT = auto()
3571        BIGSERIAL = auto()
3572        BINARY = auto()
3573        BIT = auto()
3574        BOOLEAN = auto()
3575        CHAR = auto()
3576        DATE = auto()
3577        DATEMULTIRANGE = auto()
3578        DATERANGE = auto()
3579        DATETIME = auto()
3580        DATETIME64 = auto()
3581        DECIMAL = auto()
3582        DOUBLE = auto()
3583        ENUM = auto()
3584        ENUM8 = auto()
3585        ENUM16 = auto()
3586        FIXEDSTRING = auto()
3587        FLOAT = auto()
3588        GEOGRAPHY = auto()
3589        GEOMETRY = auto()
3590        HLLSKETCH = auto()
3591        HSTORE = auto()
3592        IMAGE = auto()
3593        INET = auto()
3594        INT = auto()
3595        INT128 = auto()
3596        INT256 = auto()
3597        INT4MULTIRANGE = auto()
3598        INT4RANGE = auto()
3599        INT8MULTIRANGE = auto()
3600        INT8RANGE = auto()
3601        INTERVAL = auto()
3602        IPADDRESS = auto()
3603        IPPREFIX = auto()
3604        JSON = auto()
3605        JSONB = auto()
3606        LONGBLOB = auto()
3607        LONGTEXT = auto()
3608        LOWCARDINALITY = auto()
3609        MAP = auto()
3610        MEDIUMBLOB = auto()
3611        MEDIUMINT = auto()
3612        MEDIUMTEXT = auto()
3613        MONEY = auto()
3614        NCHAR = auto()
3615        NESTED = auto()
3616        NULL = auto()
3617        NULLABLE = auto()
3618        NUMMULTIRANGE = auto()
3619        NUMRANGE = auto()
3620        NVARCHAR = auto()
3621        OBJECT = auto()
3622        ROWVERSION = auto()
3623        SERIAL = auto()
3624        SET = auto()
3625        SMALLINT = auto()
3626        SMALLMONEY = auto()
3627        SMALLSERIAL = auto()
3628        STRUCT = auto()
3629        SUPER = auto()
3630        TEXT = auto()
3631        TINYBLOB = auto()
3632        TINYTEXT = auto()
3633        TIME = auto()
3634        TIMETZ = auto()
3635        TIMESTAMP = auto()
3636        TIMESTAMPLTZ = auto()
3637        TIMESTAMPTZ = auto()
3638        TIMESTAMP_S = auto()
3639        TIMESTAMP_MS = auto()
3640        TIMESTAMP_NS = auto()
3641        TINYINT = auto()
3642        TSMULTIRANGE = auto()
3643        TSRANGE = auto()
3644        TSTZMULTIRANGE = auto()
3645        TSTZRANGE = auto()
3646        UBIGINT = auto()
3647        UINT = auto()
3648        UINT128 = auto()
3649        UINT256 = auto()
3650        UMEDIUMINT = auto()
3651        UDECIMAL = auto()
3652        UNIQUEIDENTIFIER = auto()
3653        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3654        USERDEFINED = "USER-DEFINED"
3655        USMALLINT = auto()
3656        UTINYINT = auto()
3657        UUID = auto()
3658        VARBINARY = auto()
3659        VARCHAR = auto()
3660        VARIANT = auto()
3661        XML = auto()
3662        YEAR = auto()
3663
3664    TEXT_TYPES = {
3665        Type.CHAR,
3666        Type.NCHAR,
3667        Type.VARCHAR,
3668        Type.NVARCHAR,
3669        Type.TEXT,
3670    }
3671
3672    INTEGER_TYPES = {
3673        Type.INT,
3674        Type.TINYINT,
3675        Type.SMALLINT,
3676        Type.BIGINT,
3677        Type.INT128,
3678        Type.INT256,
3679    }
3680
3681    FLOAT_TYPES = {
3682        Type.FLOAT,
3683        Type.DOUBLE,
3684    }
3685
3686    NUMERIC_TYPES = {
3687        *INTEGER_TYPES,
3688        *FLOAT_TYPES,
3689    }
3690
3691    TEMPORAL_TYPES = {
3692        Type.TIME,
3693        Type.TIMETZ,
3694        Type.TIMESTAMP,
3695        Type.TIMESTAMPTZ,
3696        Type.TIMESTAMPLTZ,
3697        Type.TIMESTAMP_S,
3698        Type.TIMESTAMP_MS,
3699        Type.TIMESTAMP_NS,
3700        Type.DATE,
3701        Type.DATETIME,
3702        Type.DATETIME64,
3703    }
3704
3705    @classmethod
3706    def build(
3707        cls,
3708        dtype: str | DataType | DataType.Type,
3709        dialect: DialectType = None,
3710        udt: bool = False,
3711        **kwargs,
3712    ) -> DataType:
3713        """
3714        Constructs a DataType object.
3715
3716        Args:
3717            dtype: the data type of interest.
3718            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3719            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3720                DataType, thus creating a user-defined type.
3721            kawrgs: additional arguments to pass in the constructor of DataType.
3722
3723        Returns:
3724            The constructed DataType object.
3725        """
3726        from sqlglot import parse_one
3727
3728        if isinstance(dtype, str):
3729            if dtype.upper() == "UNKNOWN":
3730                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3731
3732            try:
3733                data_type_exp = parse_one(
3734                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3735                )
3736            except ParseError:
3737                if udt:
3738                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3739                raise
3740        elif isinstance(dtype, DataType.Type):
3741            data_type_exp = DataType(this=dtype)
3742        elif isinstance(dtype, DataType):
3743            return dtype
3744        else:
3745            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3746
3747        return DataType(**{**data_type_exp.args, **kwargs})
3748
3749    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3750        """
3751        Checks whether this DataType matches one of the provided data types. Nested types or precision
3752        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3753
3754        Args:
3755            dtypes: the data types to compare this DataType to.
3756
3757        Returns:
3758            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3759        """
3760        for dtype in dtypes:
3761            other = DataType.build(dtype, udt=True)
3762
3763            if (
3764                other.expressions
3765                or self.this == DataType.Type.USERDEFINED
3766                or other.this == DataType.Type.USERDEFINED
3767            ):
3768                matches = self == other
3769            else:
3770                matches = self.this == other.this
3771
3772            if matches:
3773                return True
3774        return False
3775
3776
3777# https://www.postgresql.org/docs/15/datatype-pseudo.html
3778class PseudoType(DataType):
3779    arg_types = {"this": True}
3780
3781
3782# https://www.postgresql.org/docs/15/datatype-oid.html
3783class ObjectIdentifier(DataType):
3784    arg_types = {"this": True}
3785
3786
3787# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3788class SubqueryPredicate(Predicate):
3789    pass
3790
3791
3792class All(SubqueryPredicate):
3793    pass
3794
3795
3796class Any(SubqueryPredicate):
3797    pass
3798
3799
3800class Exists(SubqueryPredicate):
3801    pass
3802
3803
3804# Commands to interact with the databases or engines. For most of the command
3805# expressions we parse whatever comes after the command's name as a string.
3806class Command(Expression):
3807    arg_types = {"this": True, "expression": False}
3808
3809
3810class Transaction(Expression):
3811    arg_types = {"this": False, "modes": False, "mark": False}
3812
3813
3814class Commit(Expression):
3815    arg_types = {"chain": False, "this": False, "durability": False}
3816
3817
3818class Rollback(Expression):
3819    arg_types = {"savepoint": False, "this": False}
3820
3821
3822class AlterTable(Expression):
3823    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3824
3825
3826class AddConstraint(Expression):
3827    arg_types = {"this": False, "expression": False, "enforced": False}
3828
3829
3830class DropPartition(Expression):
3831    arg_types = {"expressions": True, "exists": False}
3832
3833
3834# Binary expressions like (ADD a b)
3835class Binary(Condition):
3836    arg_types = {"this": True, "expression": True}
3837
3838    @property
3839    def left(self) -> Expression:
3840        return self.this
3841
3842    @property
3843    def right(self) -> Expression:
3844        return self.expression
3845
3846
3847class Add(Binary):
3848    pass
3849
3850
3851class Connector(Binary):
3852    pass
3853
3854
3855class And(Connector):
3856    pass
3857
3858
3859class Or(Connector):
3860    pass
3861
3862
3863class BitwiseAnd(Binary):
3864    pass
3865
3866
3867class BitwiseLeftShift(Binary):
3868    pass
3869
3870
3871class BitwiseOr(Binary):
3872    pass
3873
3874
3875class BitwiseRightShift(Binary):
3876    pass
3877
3878
3879class BitwiseXor(Binary):
3880    pass
3881
3882
3883class Div(Binary):
3884    pass
3885
3886
3887class Overlaps(Binary):
3888    pass
3889
3890
3891class Dot(Binary):
3892    @property
3893    def name(self) -> str:
3894        return self.expression.name
3895
3896    @property
3897    def output_name(self) -> str:
3898        return self.name
3899
3900    @classmethod
3901    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3902        """Build a Dot object with a sequence of expressions."""
3903        if len(expressions) < 2:
3904            raise ValueError(f"Dot requires >= 2 expressions.")
3905
3906        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3907
3908
3909class DPipe(Binary):
3910    pass
3911
3912
3913class SafeDPipe(DPipe):
3914    pass
3915
3916
3917class EQ(Binary, Predicate):
3918    pass
3919
3920
3921class NullSafeEQ(Binary, Predicate):
3922    pass
3923
3924
3925class NullSafeNEQ(Binary, Predicate):
3926    pass
3927
3928
3929class Distance(Binary):
3930    pass
3931
3932
3933class Escape(Binary):
3934    pass
3935
3936
3937class Glob(Binary, Predicate):
3938    pass
3939
3940
3941class GT(Binary, Predicate):
3942    pass
3943
3944
3945class GTE(Binary, Predicate):
3946    pass
3947
3948
3949class ILike(Binary, Predicate):
3950    pass
3951
3952
3953class ILikeAny(Binary, Predicate):
3954    pass
3955
3956
3957class IntDiv(Binary):
3958    pass
3959
3960
3961class Is(Binary, Predicate):
3962    pass
3963
3964
3965class Kwarg(Binary):
3966    """Kwarg in special functions like func(kwarg => y)."""
3967
3968
3969class Like(Binary, Predicate):
3970    pass
3971
3972
3973class LikeAny(Binary, Predicate):
3974    pass
3975
3976
3977class LT(Binary, Predicate):
3978    pass
3979
3980
3981class LTE(Binary, Predicate):
3982    pass
3983
3984
3985class Mod(Binary):
3986    pass
3987
3988
3989class Mul(Binary):
3990    pass
3991
3992
3993class NEQ(Binary, Predicate):
3994    pass
3995
3996
3997class SimilarTo(Binary, Predicate):
3998    pass
3999
4000
4001class Slice(Binary):
4002    arg_types = {"this": False, "expression": False}
4003
4004
4005class Sub(Binary):
4006    pass
4007
4008
4009class ArrayOverlaps(Binary):
4010    pass
4011
4012
4013# Unary Expressions
4014# (NOT a)
4015class Unary(Condition):
4016    pass
4017
4018
4019class BitwiseNot(Unary):
4020    pass
4021
4022
4023class Not(Unary):
4024    pass
4025
4026
4027class Paren(Unary):
4028    arg_types = {"this": True, "with": False}
4029
4030    @property
4031    def output_name(self) -> str:
4032        return self.this.name
4033
4034
4035class Neg(Unary):
4036    pass
4037
4038
4039class Alias(Expression):
4040    arg_types = {"this": True, "alias": False}
4041
4042    @property
4043    def output_name(self) -> str:
4044        return self.alias
4045
4046
4047class Aliases(Expression):
4048    arg_types = {"this": True, "expressions": True}
4049
4050    @property
4051    def aliases(self):
4052        return self.expressions
4053
4054
4055class AtTimeZone(Expression):
4056    arg_types = {"this": True, "zone": True}
4057
4058
4059class Between(Predicate):
4060    arg_types = {"this": True, "low": True, "high": True}
4061
4062
4063class Bracket(Condition):
4064    arg_types = {"this": True, "expressions": True}
4065
4066    @property
4067    def output_name(self) -> str:
4068        if len(self.expressions) == 1:
4069            return self.expressions[0].output_name
4070
4071        return super().output_name
4072
4073
4074class SafeBracket(Bracket):
4075    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4076
4077
4078class Distinct(Expression):
4079    arg_types = {"expressions": False, "on": False}
4080
4081
4082class In(Predicate):
4083    arg_types = {
4084        "this": True,
4085        "expressions": False,
4086        "query": False,
4087        "unnest": False,
4088        "field": False,
4089        "is_global": False,
4090    }
4091
4092
4093class TimeUnit(Expression):
4094    """Automatically converts unit arg into a var."""
4095
4096    arg_types = {"unit": False}
4097
4098    UNABBREVIATED_UNIT_NAME = {
4099        "d": "day",
4100        "h": "hour",
4101        "m": "minute",
4102        "ms": "millisecond",
4103        "ns": "nanosecond",
4104        "q": "quarter",
4105        "s": "second",
4106        "us": "microsecond",
4107        "w": "week",
4108        "y": "year",
4109    }
4110
4111    VAR_LIKE = (Column, Literal, Var)
4112
4113    def __init__(self, **args):
4114        unit = args.get("unit")
4115        if isinstance(unit, self.VAR_LIKE):
4116            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4117        elif isinstance(unit, Week):
4118            unit.set("this", Var(this=unit.this.name))
4119
4120        super().__init__(**args)
4121
4122    @property
4123    def unit(self) -> t.Optional[Var]:
4124        return self.args.get("unit")
4125
4126
4127class IntervalOp(TimeUnit):
4128    arg_types = {"unit": True, "expression": True}
4129
4130    def interval(self):
4131        return Interval(
4132            this=self.expression.copy(),
4133            unit=self.unit.copy(),
4134        )
4135
4136
4137# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4138# https://trino.io/docs/current/language/types.html#interval-day-to-second
4139# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4140class IntervalSpan(DataType):
4141    arg_types = {"this": True, "expression": True}
4142
4143
4144class Interval(TimeUnit):
4145    arg_types = {"this": False, "unit": False}
4146
4147
4148class IgnoreNulls(Expression):
4149    pass
4150
4151
4152class RespectNulls(Expression):
4153    pass
4154
4155
4156# Functions
4157class Func(Condition):
4158    """
4159    The base class for all function expressions.
4160
4161    Attributes:
4162        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4163            treated as a variable length argument and the argument's value will be stored as a list.
4164        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4165            for this function expression. These values are used to map this node to a name during parsing
4166            as well as to provide the function's name during SQL string generation. By default the SQL
4167            name is set to the expression's class name transformed to snake case.
4168    """
4169
4170    is_var_len_args = False
4171
4172    @classmethod
4173    def from_arg_list(cls, args):
4174        if cls.is_var_len_args:
4175            all_arg_keys = list(cls.arg_types)
4176            # If this function supports variable length argument treat the last argument as such.
4177            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4178            num_non_var = len(non_var_len_arg_keys)
4179
4180            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4181            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4182        else:
4183            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4184
4185        return cls(**args_dict)
4186
4187    @classmethod
4188    def sql_names(cls):
4189        if cls is Func:
4190            raise NotImplementedError(
4191                "SQL name is only supported by concrete function implementations"
4192            )
4193        if "_sql_names" not in cls.__dict__:
4194            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4195        return cls._sql_names
4196
4197    @classmethod
4198    def sql_name(cls):
4199        return cls.sql_names()[0]
4200
4201    @classmethod
4202    def default_parser_mappings(cls):
4203        return {name: cls.from_arg_list for name in cls.sql_names()}
4204
4205
4206class AggFunc(Func):
4207    pass
4208
4209
4210class ParameterizedAgg(AggFunc):
4211    arg_types = {"this": True, "expressions": True, "params": True}
4212
4213
4214class Abs(Func):
4215    pass
4216
4217
4218class ArgMax(AggFunc):
4219    arg_types = {"this": True, "expression": True, "count": False}
4220    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4221
4222
4223class ArgMin(AggFunc):
4224    arg_types = {"this": True, "expression": True, "count": False}
4225    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4226
4227
4228class ApproxTopK(AggFunc):
4229    arg_types = {"this": True, "expression": False, "counters": False}
4230
4231
4232class Flatten(Func):
4233    pass
4234
4235
4236# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4237class Transform(Func):
4238    arg_types = {"this": True, "expression": True}
4239
4240
4241class Anonymous(Func):
4242    arg_types = {"this": True, "expressions": False}
4243    is_var_len_args = True
4244
4245
4246# https://docs.snowflake.com/en/sql-reference/functions/hll
4247# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4248class Hll(AggFunc):
4249    arg_types = {"this": True, "expressions": False}
4250    is_var_len_args = True
4251
4252
4253class ApproxDistinct(AggFunc):
4254    arg_types = {"this": True, "accuracy": False}
4255    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4256
4257
4258class Array(Func):
4259    arg_types = {"expressions": False}
4260    is_var_len_args = True
4261
4262
4263# https://docs.snowflake.com/en/sql-reference/functions/to_char
4264class ToChar(Func):
4265    arg_types = {"this": True, "format": False}
4266
4267
4268class GenerateSeries(Func):
4269    arg_types = {"start": True, "end": True, "step": False}
4270
4271
4272class ArrayAgg(AggFunc):
4273    pass
4274
4275
4276class ArrayAll(Func):
4277    arg_types = {"this": True, "expression": True}
4278
4279
4280class ArrayAny(Func):
4281    arg_types = {"this": True, "expression": True}
4282
4283
4284class ArrayConcat(Func):
4285    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4286    arg_types = {"this": True, "expressions": False}
4287    is_var_len_args = True
4288
4289
4290class ArrayContains(Binary, Func):
4291    pass
4292
4293
4294class ArrayContained(Binary):
4295    pass
4296
4297
4298class ArrayFilter(Func):
4299    arg_types = {"this": True, "expression": True}
4300    _sql_names = ["FILTER", "ARRAY_FILTER"]
4301
4302
4303class ArrayJoin(Func):
4304    arg_types = {"this": True, "expression": True, "null": False}
4305
4306
4307class ArraySize(Func):
4308    arg_types = {"this": True, "expression": False}
4309
4310
4311class ArraySort(Func):
4312    arg_types = {"this": True, "expression": False}
4313
4314
4315class ArraySum(Func):
4316    pass
4317
4318
4319class ArrayUnionAgg(AggFunc):
4320    pass
4321
4322
4323class Avg(AggFunc):
4324    pass
4325
4326
4327class AnyValue(AggFunc):
4328    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4329
4330
4331class First(Func):
4332    arg_types = {"this": True, "ignore_nulls": False}
4333
4334
4335class Last(Func):
4336    arg_types = {"this": True, "ignore_nulls": False}
4337
4338
4339class Case(Func):
4340    arg_types = {"this": False, "ifs": True, "default": False}
4341
4342    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4343        instance = maybe_copy(self, copy)
4344        instance.append(
4345            "ifs",
4346            If(
4347                this=maybe_parse(condition, copy=copy, **opts),
4348                true=maybe_parse(then, copy=copy, **opts),
4349            ),
4350        )
4351        return instance
4352
4353    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4354        instance = maybe_copy(self, copy)
4355        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4356        return instance
4357
4358
4359class Cast(Func):
4360    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4361
4362    @property
4363    def name(self) -> str:
4364        return self.this.name
4365
4366    @property
4367    def to(self) -> DataType:
4368        return self.args["to"]
4369
4370    @property
4371    def output_name(self) -> str:
4372        return self.name
4373
4374    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4375        """
4376        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4377        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4378        array<int> != array<float>.
4379
4380        Args:
4381            dtypes: the data types to compare this Cast's DataType to.
4382
4383        Returns:
4384            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4385        """
4386        return self.to.is_type(*dtypes)
4387
4388
4389class TryCast(Cast):
4390    pass
4391
4392
4393class CastToStrType(Func):
4394    arg_types = {"this": True, "to": True}
4395
4396
4397class Collate(Binary, Func):
4398    pass
4399
4400
4401class Ceil(Func):
4402    arg_types = {"this": True, "decimals": False}
4403    _sql_names = ["CEIL", "CEILING"]
4404
4405
4406class Coalesce(Func):
4407    arg_types = {"this": True, "expressions": False}
4408    is_var_len_args = True
4409    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4410
4411
4412class Chr(Func):
4413    arg_types = {"this": True, "charset": False, "expressions": False}
4414    is_var_len_args = True
4415    _sql_names = ["CHR", "CHAR"]
4416
4417
4418class Concat(Func):
4419    arg_types = {"expressions": True}
4420    is_var_len_args = True
4421
4422
4423class SafeConcat(Concat):
4424    pass
4425
4426
4427class ConcatWs(Concat):
4428    _sql_names = ["CONCAT_WS"]
4429
4430
4431class Count(AggFunc):
4432    arg_types = {"this": False, "expressions": False}
4433    is_var_len_args = True
4434
4435
4436class CountIf(AggFunc):
4437    pass
4438
4439
4440class CurrentDate(Func):
4441    arg_types = {"this": False}
4442
4443
4444class CurrentDatetime(Func):
4445    arg_types = {"this": False}
4446
4447
4448class CurrentTime(Func):
4449    arg_types = {"this": False}
4450
4451
4452class CurrentTimestamp(Func):
4453    arg_types = {"this": False}
4454
4455
4456class CurrentUser(Func):
4457    arg_types = {"this": False}
4458
4459
4460class DateAdd(Func, IntervalOp):
4461    arg_types = {"this": True, "expression": True, "unit": False}
4462
4463
4464class DateSub(Func, IntervalOp):
4465    arg_types = {"this": True, "expression": True, "unit": False}
4466
4467
4468class DateDiff(Func, TimeUnit):
4469    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4470    arg_types = {"this": True, "expression": True, "unit": False}
4471
4472
4473class DateTrunc(Func):
4474    arg_types = {"unit": True, "this": True, "zone": False}
4475
4476    @property
4477    def unit(self) -> Expression:
4478        return self.args["unit"]
4479
4480
4481class DatetimeAdd(Func, IntervalOp):
4482    arg_types = {"this": True, "expression": True, "unit": False}
4483
4484
4485class DatetimeSub(Func, IntervalOp):
4486    arg_types = {"this": True, "expression": True, "unit": False}
4487
4488
4489class DatetimeDiff(Func, TimeUnit):
4490    arg_types = {"this": True, "expression": True, "unit": False}
4491
4492
4493class DatetimeTrunc(Func, TimeUnit):
4494    arg_types = {"this": True, "unit": True, "zone": False}
4495
4496
4497class DayOfWeek(Func):
4498    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4499
4500
4501class DayOfMonth(Func):
4502    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4503
4504
4505class DayOfYear(Func):
4506    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4507
4508
4509class ToDays(Func):
4510    pass
4511
4512
4513class WeekOfYear(Func):
4514    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4515
4516
4517class MonthsBetween(Func):
4518    arg_types = {"this": True, "expression": True, "roundoff": False}
4519
4520
4521class LastDateOfMonth(Func):
4522    pass
4523
4524
4525class Extract(Func):
4526    arg_types = {"this": True, "expression": True}
4527
4528
4529class Timestamp(Func):
4530    arg_types = {"this": False, "expression": False}
4531
4532
4533class TimestampAdd(Func, TimeUnit):
4534    arg_types = {"this": True, "expression": True, "unit": False}
4535
4536
4537class TimestampSub(Func, TimeUnit):
4538    arg_types = {"this": True, "expression": True, "unit": False}
4539
4540
4541class TimestampDiff(Func, TimeUnit):
4542    arg_types = {"this": True, "expression": True, "unit": False}
4543
4544
4545class TimestampTrunc(Func, TimeUnit):
4546    arg_types = {"this": True, "unit": True, "zone": False}
4547
4548
4549class TimeAdd(Func, TimeUnit):
4550    arg_types = {"this": True, "expression": True, "unit": False}
4551
4552
4553class TimeSub(Func, TimeUnit):
4554    arg_types = {"this": True, "expression": True, "unit": False}
4555
4556
4557class TimeDiff(Func, TimeUnit):
4558    arg_types = {"this": True, "expression": True, "unit": False}
4559
4560
4561class TimeTrunc(Func, TimeUnit):
4562    arg_types = {"this": True, "unit": True, "zone": False}
4563
4564
4565class DateFromParts(Func):
4566    _sql_names = ["DATEFROMPARTS"]
4567    arg_types = {"year": True, "month": True, "day": True}
4568
4569
4570class DateStrToDate(Func):
4571    pass
4572
4573
4574class DateToDateStr(Func):
4575    pass
4576
4577
4578class DateToDi(Func):
4579    pass
4580
4581
4582# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4583class Date(Func):
4584    arg_types = {"this": False, "zone": False, "expressions": False}
4585    is_var_len_args = True
4586
4587
4588class Day(Func):
4589    pass
4590
4591
4592class Decode(Func):
4593    arg_types = {"this": True, "charset": True, "replace": False}
4594
4595
4596class DiToDate(Func):
4597    pass
4598
4599
4600class Encode(Func):
4601    arg_types = {"this": True, "charset": True}
4602
4603
4604class Exp(Func):
4605    pass
4606
4607
4608# https://docs.snowflake.com/en/sql-reference/functions/flatten
4609class Explode(Func):
4610    arg_types = {"this": True, "expressions": False}
4611    is_var_len_args = True
4612
4613
4614class ExplodeOuter(Explode):
4615    pass
4616
4617
4618class Posexplode(Explode):
4619    pass
4620
4621
4622class PosexplodeOuter(Posexplode):
4623    pass
4624
4625
4626class Floor(Func):
4627    arg_types = {"this": True, "decimals": False}
4628
4629
4630class FromBase64(Func):
4631    pass
4632
4633
4634class ToBase64(Func):
4635    pass
4636
4637
4638class Greatest(Func):
4639    arg_types = {"this": True, "expressions": False}
4640    is_var_len_args = True
4641
4642
4643class GroupConcat(AggFunc):
4644    arg_types = {"this": True, "separator": False}
4645
4646
4647class Hex(Func):
4648    pass
4649
4650
4651class Xor(Connector, Func):
4652    arg_types = {"this": False, "expression": False, "expressions": False}
4653
4654
4655class If(Func):
4656    arg_types = {"this": True, "true": True, "false": False}
4657
4658
4659class Initcap(Func):
4660    arg_types = {"this": True, "expression": False}
4661
4662
4663class IsNan(Func):
4664    _sql_names = ["IS_NAN", "ISNAN"]
4665
4666
4667class FormatJson(Expression):
4668    pass
4669
4670
4671class JSONKeyValue(Expression):
4672    arg_types = {"this": True, "expression": True}
4673
4674
4675class JSONObject(Func):
4676    arg_types = {
4677        "expressions": False,
4678        "null_handling": False,
4679        "unique_keys": False,
4680        "return_type": False,
4681        "encoding": False,
4682    }
4683
4684
4685# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4686class JSONArray(Func):
4687    arg_types = {
4688        "expressions": True,
4689        "null_handling": False,
4690        "return_type": False,
4691        "strict": False,
4692    }
4693
4694
4695# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4696class JSONArrayAgg(Func):
4697    arg_types = {
4698        "this": True,
4699        "order": False,
4700        "null_handling": False,
4701        "return_type": False,
4702        "strict": False,
4703    }
4704
4705
4706# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4707# Note: parsing of JSON column definitions is currently incomplete.
4708class JSONColumnDef(Expression):
4709    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4710
4711
4712class JSONSchema(Expression):
4713    arg_types = {"expressions": True}
4714
4715
4716# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4717class JSONTable(Func):
4718    arg_types = {
4719        "this": True,
4720        "schema": True,
4721        "path": False,
4722        "error_handling": False,
4723        "empty_handling": False,
4724    }
4725
4726
4727class OpenJSONColumnDef(Expression):
4728    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4729
4730
4731class OpenJSON(Func):
4732    arg_types = {"this": True, "path": False, "expressions": False}
4733
4734
4735class JSONBContains(Binary):
4736    _sql_names = ["JSONB_CONTAINS"]
4737
4738
4739class JSONExtract(Binary, Func):
4740    _sql_names = ["JSON_EXTRACT"]
4741
4742
4743class JSONExtractScalar(JSONExtract):
4744    _sql_names = ["JSON_EXTRACT_SCALAR"]
4745
4746
4747class JSONBExtract(JSONExtract):
4748    _sql_names = ["JSONB_EXTRACT"]
4749
4750
4751class JSONBExtractScalar(JSONExtract):
4752    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4753
4754
4755class JSONFormat(Func):
4756    arg_types = {"this": False, "options": False}
4757    _sql_names = ["JSON_FORMAT"]
4758
4759
4760# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4761class JSONArrayContains(Binary, Predicate, Func):
4762    _sql_names = ["JSON_ARRAY_CONTAINS"]
4763
4764
4765class ParseJSON(Func):
4766    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4767    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4768    arg_types = {"this": True, "expressions": False}
4769    is_var_len_args = True
4770
4771
4772class Least(Func):
4773    arg_types = {"this": True, "expressions": False}
4774    is_var_len_args = True
4775
4776
4777class Left(Func):
4778    arg_types = {"this": True, "expression": True}
4779
4780
4781class Right(Func):
4782    arg_types = {"this": True, "expression": True}
4783
4784
4785class Length(Func):
4786    _sql_names = ["LENGTH", "LEN"]
4787
4788
4789class Levenshtein(Func):
4790    arg_types = {
4791        "this": True,
4792        "expression": False,
4793        "ins_cost": False,
4794        "del_cost": False,
4795        "sub_cost": False,
4796    }
4797
4798
4799class Ln(Func):
4800    pass
4801
4802
4803class Log(Func):
4804    arg_types = {"this": True, "expression": False}
4805
4806
4807class Log2(Func):
4808    pass
4809
4810
4811class Log10(Func):
4812    pass
4813
4814
4815class LogicalOr(AggFunc):
4816    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4817
4818
4819class LogicalAnd(AggFunc):
4820    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4821
4822
4823class Lower(Func):
4824    _sql_names = ["LOWER", "LCASE"]
4825
4826
4827class Map(Func):
4828    arg_types = {"keys": False, "values": False}
4829
4830    @property
4831    def keys(self) -> t.List[Expression]:
4832        keys = self.args.get("keys")
4833        return keys.expressions if keys else []
4834
4835    @property
4836    def values(self) -> t.List[Expression]:
4837        values = self.args.get("values")
4838        return values.expressions if values else []
4839
4840
4841class MapFromEntries(Func):
4842    pass
4843
4844
4845class StarMap(Func):
4846    pass
4847
4848
4849class VarMap(Func):
4850    arg_types = {"keys": True, "values": True}
4851    is_var_len_args = True
4852
4853    @property
4854    def keys(self) -> t.List[Expression]:
4855        return self.args["keys"].expressions
4856
4857    @property
4858    def values(self) -> t.List[Expression]:
4859        return self.args["values"].expressions
4860
4861
4862# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4863class MatchAgainst(Func):
4864    arg_types = {"this": True, "expressions": True, "modifier": False}
4865
4866
4867class Max(AggFunc):
4868    arg_types = {"this": True, "expressions": False}
4869    is_var_len_args = True
4870
4871
4872class MD5(Func):
4873    _sql_names = ["MD5"]
4874
4875
4876# Represents the variant of the MD5 function that returns a binary value
4877class MD5Digest(Func):
4878    _sql_names = ["MD5_DIGEST"]
4879
4880
4881class Min(AggFunc):
4882    arg_types = {"this": True, "expressions": False}
4883    is_var_len_args = True
4884
4885
4886class Month(Func):
4887    pass
4888
4889
4890class Nvl2(Func):
4891    arg_types = {"this": True, "true": True, "false": False}
4892
4893
4894# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4895class Predict(Func):
4896    arg_types = {"this": True, "expression": True, "params_struct": False}
4897
4898
4899class Pow(Binary, Func):
4900    _sql_names = ["POWER", "POW"]
4901
4902
4903class PercentileCont(AggFunc):
4904    arg_types = {"this": True, "expression": False}
4905
4906
4907class PercentileDisc(AggFunc):
4908    arg_types = {"this": True, "expression": False}
4909
4910
4911class Quantile(AggFunc):
4912    arg_types = {"this": True, "quantile": True}
4913
4914
4915class ApproxQuantile(Quantile):
4916    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4917
4918
4919class RangeN(Func):
4920    arg_types = {"this": True, "expressions": True, "each": False}
4921
4922
4923class ReadCSV(Func):
4924    _sql_names = ["READ_CSV"]
4925    is_var_len_args = True
4926    arg_types = {"this": True, "expressions": False}
4927
4928
4929class Reduce(Func):
4930    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4931
4932
4933class RegexpExtract(Func):
4934    arg_types = {
4935        "this": True,
4936        "expression": True,
4937        "position": False,
4938        "occurrence": False,
4939        "parameters": False,
4940        "group": False,
4941    }
4942
4943
4944class RegexpReplace(Func):
4945    arg_types = {
4946        "this": True,
4947        "expression": True,
4948        "replacement": True,
4949        "position": False,
4950        "occurrence": False,
4951        "parameters": False,
4952        "modifiers": False,
4953    }
4954
4955
4956class RegexpLike(Binary, Func):
4957    arg_types = {"this": True, "expression": True, "flag": False}
4958
4959
4960class RegexpILike(Binary, Func):
4961    arg_types = {"this": True, "expression": True, "flag": False}
4962
4963
4964# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4965# limit is the number of times a pattern is applied
4966class RegexpSplit(Func):
4967    arg_types = {"this": True, "expression": True, "limit": False}
4968
4969
4970class Repeat(Func):
4971    arg_types = {"this": True, "times": True}
4972
4973
4974class Round(Func):
4975    arg_types = {"this": True, "decimals": False}
4976
4977
4978class RowNumber(Func):
4979    arg_types: t.Dict[str, t.Any] = {}
4980
4981
4982class SafeDivide(Func):
4983    arg_types = {"this": True, "expression": True}
4984
4985
4986class SetAgg(AggFunc):
4987    pass
4988
4989
4990class SHA(Func):
4991    _sql_names = ["SHA", "SHA1"]
4992
4993
4994class SHA2(Func):
4995    _sql_names = ["SHA2"]
4996    arg_types = {"this": True, "length": False}
4997
4998
4999class SortArray(Func):
5000    arg_types = {"this": True, "asc": False}
5001
5002
5003class Split(Func):
5004    arg_types = {"this": True, "expression": True, "limit": False}
5005
5006
5007# Start may be omitted in the case of postgres
5008# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5009class Substring(Func):
5010    arg_types = {"this": True, "start": False, "length": False}
5011
5012
5013class StandardHash(Func):
5014    arg_types = {"this": True, "expression": False}
5015
5016
5017class StartsWith(Func):
5018    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5019    arg_types = {"this": True, "expression": True}
5020
5021
5022class StrPosition(Func):
5023    arg_types = {
5024        "this": True,
5025        "substr": True,
5026        "position": False,
5027        "instance": False,
5028    }
5029
5030
5031class StrToDate(Func):
5032    arg_types = {"this": True, "format": True}
5033
5034
5035class StrToTime(Func):
5036    arg_types = {"this": True, "format": True, "zone": False}
5037
5038
5039# Spark allows unix_timestamp()
5040# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5041class StrToUnix(Func):
5042    arg_types = {"this": False, "format": False}
5043
5044
5045# https://prestodb.io/docs/current/functions/string.html
5046# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5047class StrToMap(Func):
5048    arg_types = {
5049        "this": True,
5050        "pair_delim": False,
5051        "key_value_delim": False,
5052        "duplicate_resolution_callback": False,
5053    }
5054
5055
5056class NumberToStr(Func):
5057    arg_types = {"this": True, "format": True, "culture": False}
5058
5059
5060class FromBase(Func):
5061    arg_types = {"this": True, "expression": True}
5062
5063
5064class Struct(Func):
5065    arg_types = {"expressions": False}
5066    is_var_len_args = True
5067
5068
5069class StructExtract(Func):
5070    arg_types = {"this": True, "expression": True}
5071
5072
5073# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5074# https://docs.snowflake.com/en/sql-reference/functions/insert
5075class Stuff(Func):
5076    _sql_names = ["STUFF", "INSERT"]
5077    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5078
5079
5080class Sum(AggFunc):
5081    pass
5082
5083
5084class Sqrt(Func):
5085    pass
5086
5087
5088class Stddev(AggFunc):
5089    pass
5090
5091
5092class StddevPop(AggFunc):
5093    pass
5094
5095
5096class StddevSamp(AggFunc):
5097    pass
5098
5099
5100class TimeToStr(Func):
5101    arg_types = {"this": True, "format": True, "culture": False}
5102
5103
5104class TimeToTimeStr(Func):
5105    pass
5106
5107
5108class TimeToUnix(Func):
5109    pass
5110
5111
5112class TimeStrToDate(Func):
5113    pass
5114
5115
5116class TimeStrToTime(Func):
5117    pass
5118
5119
5120class TimeStrToUnix(Func):
5121    pass
5122
5123
5124class Trim(Func):
5125    arg_types = {
5126        "this": True,
5127        "expression": False,
5128        "position": False,
5129        "collation": False,
5130    }
5131
5132
5133class TsOrDsAdd(Func, TimeUnit):
5134    arg_types = {"this": True, "expression": True, "unit": False}
5135
5136
5137class TsOrDsToDateStr(Func):
5138    pass
5139
5140
5141class TsOrDsToDate(Func):
5142    arg_types = {"this": True, "format": False}
5143
5144
5145class TsOrDiToDi(Func):
5146    pass
5147
5148
5149class Unhex(Func):
5150    pass
5151
5152
5153class UnixToStr(Func):
5154    arg_types = {"this": True, "format": False}
5155
5156
5157# https://prestodb.io/docs/current/functions/datetime.html
5158# presto has weird zone/hours/minutes
5159class UnixToTime(Func):
5160    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5161
5162    SECONDS = Literal.string("seconds")
5163    MILLIS = Literal.string("millis")
5164    MICROS = Literal.string("micros")
5165
5166
5167class UnixToTimeStr(Func):
5168    pass
5169
5170
5171class Upper(Func):
5172    _sql_names = ["UPPER", "UCASE"]
5173
5174
5175class Variance(AggFunc):
5176    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5177
5178
5179class VariancePop(AggFunc):
5180    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5181
5182
5183class Week(Func):
5184    arg_types = {"this": True, "mode": False}
5185
5186
5187class XMLTable(Func):
5188    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5189
5190
5191class Year(Func):
5192    pass
5193
5194
5195class Use(Expression):
5196    arg_types = {"this": True, "kind": False}
5197
5198
5199class Merge(Expression):
5200    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5201
5202
5203class When(Func):
5204    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5205
5206
5207# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5208# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5209class NextValueFor(Func):
5210    arg_types = {"this": True, "order": False}
5211
5212
5213def _norm_arg(arg):
5214    return arg.lower() if type(arg) is str else arg
5215
5216
5217ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5218
5219
5220# Helpers
5221@t.overload
5222def maybe_parse(
5223    sql_or_expression: ExpOrStr,
5224    *,
5225    into: t.Type[E],
5226    dialect: DialectType = None,
5227    prefix: t.Optional[str] = None,
5228    copy: bool = False,
5229    **opts,
5230) -> E:
5231    ...
5232
5233
5234@t.overload
5235def maybe_parse(
5236    sql_or_expression: str | E,
5237    *,
5238    into: t.Optional[IntoType] = None,
5239    dialect: DialectType = None,
5240    prefix: t.Optional[str] = None,
5241    copy: bool = False,
5242    **opts,
5243) -> E:
5244    ...
5245
5246
5247def maybe_parse(
5248    sql_or_expression: ExpOrStr,
5249    *,
5250    into: t.Optional[IntoType] = None,
5251    dialect: DialectType = None,
5252    prefix: t.Optional[str] = None,
5253    copy: bool = False,
5254    **opts,
5255) -> Expression:
5256    """Gracefully handle a possible string or expression.
5257
5258    Example:
5259        >>> maybe_parse("1")
5260        (LITERAL this: 1, is_string: False)
5261        >>> maybe_parse(to_identifier("x"))
5262        (IDENTIFIER this: x, quoted: False)
5263
5264    Args:
5265        sql_or_expression: the SQL code string or an expression
5266        into: the SQLGlot Expression to parse into
5267        dialect: the dialect used to parse the input expressions (in the case that an
5268            input expression is a SQL string).
5269        prefix: a string to prefix the sql with before it gets parsed
5270            (automatically includes a space)
5271        copy: whether or not to copy the expression.
5272        **opts: other options to use to parse the input expressions (again, in the case
5273            that an input expression is a SQL string).
5274
5275    Returns:
5276        Expression: the parsed or given expression.
5277    """
5278    if isinstance(sql_or_expression, Expression):
5279        if copy:
5280            return sql_or_expression.copy()
5281        return sql_or_expression
5282
5283    if sql_or_expression is None:
5284        raise ParseError(f"SQL cannot be None")
5285
5286    import sqlglot
5287
5288    sql = str(sql_or_expression)
5289    if prefix:
5290        sql = f"{prefix} {sql}"
5291
5292    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5293
5294
5295@t.overload
5296def maybe_copy(instance: None, copy: bool = True) -> None:
5297    ...
5298
5299
5300@t.overload
5301def maybe_copy(instance: E, copy: bool = True) -> E:
5302    ...
5303
5304
5305def maybe_copy(instance, copy=True):
5306    return instance.copy() if copy and instance else instance
5307
5308
5309def _is_wrong_expression(expression, into):
5310    return isinstance(expression, Expression) and not isinstance(expression, into)
5311
5312
5313def _apply_builder(
5314    expression,
5315    instance,
5316    arg,
5317    copy=True,
5318    prefix=None,
5319    into=None,
5320    dialect=None,
5321    into_arg="this",
5322    **opts,
5323):
5324    if _is_wrong_expression(expression, into):
5325        expression = into(**{into_arg: expression})
5326    instance = maybe_copy(instance, copy)
5327    expression = maybe_parse(
5328        sql_or_expression=expression,
5329        prefix=prefix,
5330        into=into,
5331        dialect=dialect,
5332        **opts,
5333    )
5334    instance.set(arg, expression)
5335    return instance
5336
5337
5338def _apply_child_list_builder(
5339    *expressions,
5340    instance,
5341    arg,
5342    append=True,
5343    copy=True,
5344    prefix=None,
5345    into=None,
5346    dialect=None,
5347    properties=None,
5348    **opts,
5349):
5350    instance = maybe_copy(instance, copy)
5351    parsed = []
5352    for expression in expressions:
5353        if expression is not None:
5354            if _is_wrong_expression(expression, into):
5355                expression = into(expressions=[expression])
5356
5357            expression = maybe_parse(
5358                expression,
5359                into=into,
5360                dialect=dialect,
5361                prefix=prefix,
5362                **opts,
5363            )
5364            parsed.extend(expression.expressions)
5365
5366    existing = instance.args.get(arg)
5367    if append and existing:
5368        parsed = existing.expressions + parsed
5369
5370    child = into(expressions=parsed)
5371    for k, v in (properties or {}).items():
5372        child.set(k, v)
5373    instance.set(arg, child)
5374
5375    return instance
5376
5377
5378def _apply_list_builder(
5379    *expressions,
5380    instance,
5381    arg,
5382    append=True,
5383    copy=True,
5384    prefix=None,
5385    into=None,
5386    dialect=None,
5387    **opts,
5388):
5389    inst = maybe_copy(instance, copy)
5390
5391    expressions = [
5392        maybe_parse(
5393            sql_or_expression=expression,
5394            into=into,
5395            prefix=prefix,
5396            dialect=dialect,
5397            **opts,
5398        )
5399        for expression in expressions
5400        if expression is not None
5401    ]
5402
5403    existing_expressions = inst.args.get(arg)
5404    if append and existing_expressions:
5405        expressions = existing_expressions + expressions
5406
5407    inst.set(arg, expressions)
5408    return inst
5409
5410
5411def _apply_conjunction_builder(
5412    *expressions,
5413    instance,
5414    arg,
5415    into=None,
5416    append=True,
5417    copy=True,
5418    dialect=None,
5419    **opts,
5420):
5421    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5422    if not expressions:
5423        return instance
5424
5425    inst = maybe_copy(instance, copy)
5426
5427    existing = inst.args.get(arg)
5428    if append and existing is not None:
5429        expressions = [existing.this if into else existing] + list(expressions)
5430
5431    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5432
5433    inst.set(arg, into(this=node) if into else node)
5434    return inst
5435
5436
5437def _apply_cte_builder(
5438    instance: E,
5439    alias: ExpOrStr,
5440    as_: ExpOrStr,
5441    recursive: t.Optional[bool] = None,
5442    append: bool = True,
5443    dialect: DialectType = None,
5444    copy: bool = True,
5445    **opts,
5446) -> E:
5447    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5448    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5449    cte = CTE(this=as_expression, alias=alias_expression)
5450    return _apply_child_list_builder(
5451        cte,
5452        instance=instance,
5453        arg="with",
5454        append=append,
5455        copy=copy,
5456        into=With,
5457        properties={"recursive": recursive or False},
5458    )
5459
5460
5461def _combine(
5462    expressions: t.Sequence[t.Optional[ExpOrStr]],
5463    operator: t.Type[Connector],
5464    dialect: DialectType = None,
5465    copy: bool = True,
5466    **opts,
5467) -> Expression:
5468    conditions = [
5469        condition(expression, dialect=dialect, copy=copy, **opts)
5470        for expression in expressions
5471        if expression is not None
5472    ]
5473
5474    this, *rest = conditions
5475    if rest:
5476        this = _wrap(this, Connector)
5477    for expression in rest:
5478        this = operator(this=this, expression=_wrap(expression, Connector))
5479
5480    return this
5481
5482
5483def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5484    return Paren(this=expression) if isinstance(expression, kind) else expression
5485
5486
5487def union(
5488    left: ExpOrStr,
5489    right: ExpOrStr,
5490    distinct: bool = True,
5491    dialect: DialectType = None,
5492    copy: bool = True,
5493    **opts,
5494) -> Union:
5495    """
5496    Initializes a syntax tree from one UNION expression.
5497
5498    Example:
5499        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5500        'SELECT * FROM foo UNION SELECT * FROM bla'
5501
5502    Args:
5503        left: the SQL code string corresponding to the left-hand side.
5504            If an `Expression` instance is passed, it will be used as-is.
5505        right: the SQL code string corresponding to the right-hand side.
5506            If an `Expression` instance is passed, it will be used as-is.
5507        distinct: set the DISTINCT flag if and only if this is true.
5508        dialect: the dialect used to parse the input expression.
5509        copy: whether or not to copy the expression.
5510        opts: other options to use to parse the input expressions.
5511
5512    Returns:
5513        The new Union instance.
5514    """
5515    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5516    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5517
5518    return Union(this=left, expression=right, distinct=distinct)
5519
5520
5521def intersect(
5522    left: ExpOrStr,
5523    right: ExpOrStr,
5524    distinct: bool = True,
5525    dialect: DialectType = None,
5526    copy: bool = True,
5527    **opts,
5528) -> Intersect:
5529    """
5530    Initializes a syntax tree from one INTERSECT expression.
5531
5532    Example:
5533        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5534        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5535
5536    Args:
5537        left: the SQL code string corresponding to the left-hand side.
5538            If an `Expression` instance is passed, it will be used as-is.
5539        right: the SQL code string corresponding to the right-hand side.
5540            If an `Expression` instance is passed, it will be used as-is.
5541        distinct: set the DISTINCT flag if and only if this is true.
5542        dialect: the dialect used to parse the input expression.
5543        copy: whether or not to copy the expression.
5544        opts: other options to use to parse the input expressions.
5545
5546    Returns:
5547        The new Intersect instance.
5548    """
5549    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5550    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5551
5552    return Intersect(this=left, expression=right, distinct=distinct)
5553
5554
5555def except_(
5556    left: ExpOrStr,
5557    right: ExpOrStr,
5558    distinct: bool = True,
5559    dialect: DialectType = None,
5560    copy: bool = True,
5561    **opts,
5562) -> Except:
5563    """
5564    Initializes a syntax tree from one EXCEPT expression.
5565
5566    Example:
5567        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5568        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5569
5570    Args:
5571        left: the SQL code string corresponding to the left-hand side.
5572            If an `Expression` instance is passed, it will be used as-is.
5573        right: the SQL code string corresponding to the right-hand side.
5574            If an `Expression` instance is passed, it will be used as-is.
5575        distinct: set the DISTINCT flag if and only if this is true.
5576        dialect: the dialect used to parse the input expression.
5577        copy: whether or not to copy the expression.
5578        opts: other options to use to parse the input expressions.
5579
5580    Returns:
5581        The new Except instance.
5582    """
5583    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5584    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5585
5586    return Except(this=left, expression=right, distinct=distinct)
5587
5588
5589def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5590    """
5591    Initializes a syntax tree from one or multiple SELECT expressions.
5592
5593    Example:
5594        >>> select("col1", "col2").from_("tbl").sql()
5595        'SELECT col1, col2 FROM tbl'
5596
5597    Args:
5598        *expressions: the SQL code string to parse as the expressions of a
5599            SELECT statement. If an Expression instance is passed, this is used as-is.
5600        dialect: the dialect used to parse the input expressions (in the case that an
5601            input expression is a SQL string).
5602        **opts: other options to use to parse the input expressions (again, in the case
5603            that an input expression is a SQL string).
5604
5605    Returns:
5606        Select: the syntax tree for the SELECT statement.
5607    """
5608    return Select().select(*expressions, dialect=dialect, **opts)
5609
5610
5611def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5612    """
5613    Initializes a syntax tree from a FROM expression.
5614
5615    Example:
5616        >>> from_("tbl").select("col1", "col2").sql()
5617        'SELECT col1, col2 FROM tbl'
5618
5619    Args:
5620        *expression: the SQL code string to parse as the FROM expressions of a
5621            SELECT statement. If an Expression instance is passed, this is used as-is.
5622        dialect: the dialect used to parse the input expression (in the case that the
5623            input expression is a SQL string).
5624        **opts: other options to use to parse the input expressions (again, in the case
5625            that the input expression is a SQL string).
5626
5627    Returns:
5628        Select: the syntax tree for the SELECT statement.
5629    """
5630    return Select().from_(expression, dialect=dialect, **opts)
5631
5632
5633def update(
5634    table: str | Table,
5635    properties: dict,
5636    where: t.Optional[ExpOrStr] = None,
5637    from_: t.Optional[ExpOrStr] = None,
5638    dialect: DialectType = None,
5639    **opts,
5640) -> Update:
5641    """
5642    Creates an update statement.
5643
5644    Example:
5645        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5646        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5647
5648    Args:
5649        *properties: dictionary of properties to set which are
5650            auto converted to sql objects eg None -> NULL
5651        where: sql conditional parsed into a WHERE statement
5652        from_: sql statement parsed into a FROM statement
5653        dialect: the dialect used to parse the input expressions.
5654        **opts: other options to use to parse the input expressions.
5655
5656    Returns:
5657        Update: the syntax tree for the UPDATE statement.
5658    """
5659    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5660    update_expr.set(
5661        "expressions",
5662        [
5663            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5664            for k, v in properties.items()
5665        ],
5666    )
5667    if from_:
5668        update_expr.set(
5669            "from",
5670            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5671        )
5672    if isinstance(where, Condition):
5673        where = Where(this=where)
5674    if where:
5675        update_expr.set(
5676            "where",
5677            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5678        )
5679    return update_expr
5680
5681
5682def delete(
5683    table: ExpOrStr,
5684    where: t.Optional[ExpOrStr] = None,
5685    returning: t.Optional[ExpOrStr] = None,
5686    dialect: DialectType = None,
5687    **opts,
5688) -> Delete:
5689    """
5690    Builds a delete statement.
5691
5692    Example:
5693        >>> delete("my_table", where="id > 1").sql()
5694        'DELETE FROM my_table WHERE id > 1'
5695
5696    Args:
5697        where: sql conditional parsed into a WHERE statement
5698        returning: sql conditional parsed into a RETURNING statement
5699        dialect: the dialect used to parse the input expressions.
5700        **opts: other options to use to parse the input expressions.
5701
5702    Returns:
5703        Delete: the syntax tree for the DELETE statement.
5704    """
5705    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5706    if where:
5707        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5708    if returning:
5709        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5710    return delete_expr
5711
5712
5713def insert(
5714    expression: ExpOrStr,
5715    into: ExpOrStr,
5716    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5717    overwrite: t.Optional[bool] = None,
5718    dialect: DialectType = None,
5719    copy: bool = True,
5720    **opts,
5721) -> Insert:
5722    """
5723    Builds an INSERT statement.
5724
5725    Example:
5726        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5727        'INSERT INTO tbl VALUES (1, 2, 3)'
5728
5729    Args:
5730        expression: the sql string or expression of the INSERT statement
5731        into: the tbl to insert data to.
5732        columns: optionally the table's column names.
5733        overwrite: whether to INSERT OVERWRITE or not.
5734        dialect: the dialect used to parse the input expressions.
5735        copy: whether or not to copy the expression.
5736        **opts: other options to use to parse the input expressions.
5737
5738    Returns:
5739        Insert: the syntax tree for the INSERT statement.
5740    """
5741    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5742    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5743
5744    if columns:
5745        this = _apply_list_builder(
5746            *columns,
5747            instance=Schema(this=this),
5748            arg="expressions",
5749            into=Identifier,
5750            copy=False,
5751            dialect=dialect,
5752            **opts,
5753        )
5754
5755    return Insert(this=this, expression=expr, overwrite=overwrite)
5756
5757
5758def condition(
5759    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5760) -> Condition:
5761    """
5762    Initialize a logical condition expression.
5763
5764    Example:
5765        >>> condition("x=1").sql()
5766        'x = 1'
5767
5768        This is helpful for composing larger logical syntax trees:
5769        >>> where = condition("x=1")
5770        >>> where = where.and_("y=1")
5771        >>> Select().from_("tbl").select("*").where(where).sql()
5772        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5773
5774    Args:
5775        *expression: the SQL code string to parse.
5776            If an Expression instance is passed, this is used as-is.
5777        dialect: the dialect used to parse the input expression (in the case that the
5778            input expression is a SQL string).
5779        copy: Whether or not to copy `expression` (only applies to expressions).
5780        **opts: other options to use to parse the input expressions (again, in the case
5781            that the input expression is a SQL string).
5782
5783    Returns:
5784        The new Condition instance
5785    """
5786    return maybe_parse(
5787        expression,
5788        into=Condition,
5789        dialect=dialect,
5790        copy=copy,
5791        **opts,
5792    )
5793
5794
5795def and_(
5796    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5797) -> Condition:
5798    """
5799    Combine multiple conditions with an AND logical operator.
5800
5801    Example:
5802        >>> and_("x=1", and_("y=1", "z=1")).sql()
5803        'x = 1 AND (y = 1 AND z = 1)'
5804
5805    Args:
5806        *expressions: the SQL code strings to parse.
5807            If an Expression instance is passed, this is used as-is.
5808        dialect: the dialect used to parse the input expression.
5809        copy: whether or not to copy `expressions` (only applies to Expressions).
5810        **opts: other options to use to parse the input expressions.
5811
5812    Returns:
5813        And: the new condition
5814    """
5815    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5816
5817
5818def or_(
5819    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5820) -> Condition:
5821    """
5822    Combine multiple conditions with an OR logical operator.
5823
5824    Example:
5825        >>> or_("x=1", or_("y=1", "z=1")).sql()
5826        'x = 1 OR (y = 1 OR z = 1)'
5827
5828    Args:
5829        *expressions: the SQL code strings to parse.
5830            If an Expression instance is passed, this is used as-is.
5831        dialect: the dialect used to parse the input expression.
5832        copy: whether or not to copy `expressions` (only applies to Expressions).
5833        **opts: other options to use to parse the input expressions.
5834
5835    Returns:
5836        Or: the new condition
5837    """
5838    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5839
5840
5841def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5842    """
5843    Wrap a condition with a NOT operator.
5844
5845    Example:
5846        >>> not_("this_suit='black'").sql()
5847        "NOT this_suit = 'black'"
5848
5849    Args:
5850        expression: the SQL code string to parse.
5851            If an Expression instance is passed, this is used as-is.
5852        dialect: the dialect used to parse the input expression.
5853        copy: whether to copy the expression or not.
5854        **opts: other options to use to parse the input expressions.
5855
5856    Returns:
5857        The new condition.
5858    """
5859    this = condition(
5860        expression,
5861        dialect=dialect,
5862        copy=copy,
5863        **opts,
5864    )
5865    return Not(this=_wrap(this, Connector))
5866
5867
5868def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5869    """
5870    Wrap an expression in parentheses.
5871
5872    Example:
5873        >>> paren("5 + 3").sql()
5874        '(5 + 3)'
5875
5876    Args:
5877        expression: the SQL code string to parse.
5878            If an Expression instance is passed, this is used as-is.
5879        copy: whether to copy the expression or not.
5880
5881    Returns:
5882        The wrapped expression.
5883    """
5884    return Paren(this=maybe_parse(expression, copy=copy))
5885
5886
5887SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5888
5889
5890@t.overload
5891def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5892    ...
5893
5894
5895@t.overload
5896def to_identifier(
5897    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5898) -> Identifier:
5899    ...
5900
5901
5902def to_identifier(name, quoted=None, copy=True):
5903    """Builds an identifier.
5904
5905    Args:
5906        name: The name to turn into an identifier.
5907        quoted: Whether or not force quote the identifier.
5908        copy: Whether or not to copy name if it's an Identifier.
5909
5910    Returns:
5911        The identifier ast node.
5912    """
5913
5914    if name is None:
5915        return None
5916
5917    if isinstance(name, Identifier):
5918        identifier = maybe_copy(name, copy)
5919    elif isinstance(name, str):
5920        identifier = Identifier(
5921            this=name,
5922            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5923        )
5924    else:
5925        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5926    return identifier
5927
5928
5929def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
5930    """
5931    Parses a given string into an identifier.
5932
5933    Args:
5934        name: The name to parse into an identifier.
5935        dialect: The dialect to parse against.
5936
5937    Returns:
5938        The identifier ast node.
5939    """
5940    try:
5941        expression = maybe_parse(name, dialect=dialect, into=Identifier)
5942    except ParseError:
5943        expression = to_identifier(name)
5944
5945    return expression
5946
5947
5948INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5949
5950
5951def to_interval(interval: str | Literal) -> Interval:
5952    """Builds an interval expression from a string like '1 day' or '5 months'."""
5953    if isinstance(interval, Literal):
5954        if not interval.is_string:
5955            raise ValueError("Invalid interval string.")
5956
5957        interval = interval.this
5958
5959    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5960
5961    if not interval_parts:
5962        raise ValueError("Invalid interval string.")
5963
5964    return Interval(
5965        this=Literal.string(interval_parts.group(1)),
5966        unit=Var(this=interval_parts.group(2)),
5967    )
5968
5969
5970@t.overload
5971def to_table(sql_path: str | Table, **kwargs) -> Table:
5972    ...
5973
5974
5975@t.overload
5976def to_table(sql_path: None, **kwargs) -> None:
5977    ...
5978
5979
5980def to_table(
5981    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5982) -> t.Optional[Table]:
5983    """
5984    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5985    If a table is passed in then that table is returned.
5986
5987    Args:
5988        sql_path: a `[catalog].[schema].[table]` string.
5989        dialect: the source dialect according to which the table name will be parsed.
5990        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5991
5992    Returns:
5993        A table expression.
5994    """
5995    if sql_path is None or isinstance(sql_path, Table):
5996        return sql_path
5997    if not isinstance(sql_path, str):
5998        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5999
6000    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6001    if table:
6002        for k, v in kwargs.items():
6003            table.set(k, v)
6004
6005    return table
6006
6007
6008def to_column(sql_path: str | Column, **kwargs) -> Column:
6009    """
6010    Create a column from a `[table].[column]` sql path. Schema is optional.
6011
6012    If a column is passed in then that column is returned.
6013
6014    Args:
6015        sql_path: `[table].[column]` string
6016    Returns:
6017        Table: A column expression
6018    """
6019    if sql_path is None or isinstance(sql_path, Column):
6020        return sql_path
6021    if not isinstance(sql_path, str):
6022        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6023    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6024
6025
6026def alias_(
6027    expression: ExpOrStr,
6028    alias: str | Identifier,
6029    table: bool | t.Sequence[str | Identifier] = False,
6030    quoted: t.Optional[bool] = None,
6031    dialect: DialectType = None,
6032    copy: bool = True,
6033    **opts,
6034):
6035    """Create an Alias expression.
6036
6037    Example:
6038        >>> alias_('foo', 'bar').sql()
6039        'foo AS bar'
6040
6041        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6042        '(SELECT 1, 2) AS bar(a, b)'
6043
6044    Args:
6045        expression: the SQL code strings to parse.
6046            If an Expression instance is passed, this is used as-is.
6047        alias: the alias name to use. If the name has
6048            special characters it is quoted.
6049        table: Whether or not to create a table alias, can also be a list of columns.
6050        quoted: whether or not to quote the alias
6051        dialect: the dialect used to parse the input expression.
6052        copy: Whether or not to copy the expression.
6053        **opts: other options to use to parse the input expressions.
6054
6055    Returns:
6056        Alias: the aliased expression
6057    """
6058    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6059    alias = to_identifier(alias, quoted=quoted)
6060
6061    if table:
6062        table_alias = TableAlias(this=alias)
6063        exp.set("alias", table_alias)
6064
6065        if not isinstance(table, bool):
6066            for column in table:
6067                table_alias.append("columns", to_identifier(column, quoted=quoted))
6068
6069        return exp
6070
6071    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6072    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6073    # for the complete Window expression.
6074    #
6075    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6076
6077    if "alias" in exp.arg_types and not isinstance(exp, Window):
6078        exp.set("alias", alias)
6079        return exp
6080    return Alias(this=exp, alias=alias)
6081
6082
6083def subquery(
6084    expression: ExpOrStr,
6085    alias: t.Optional[Identifier | str] = None,
6086    dialect: DialectType = None,
6087    **opts,
6088) -> Select:
6089    """
6090    Build a subquery expression.
6091
6092    Example:
6093        >>> subquery('select x from tbl', 'bar').select('x').sql()
6094        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6095
6096    Args:
6097        expression: the SQL code strings to parse.
6098            If an Expression instance is passed, this is used as-is.
6099        alias: the alias name to use.
6100        dialect: the dialect used to parse the input expression.
6101        **opts: other options to use to parse the input expressions.
6102
6103    Returns:
6104        A new Select instance with the subquery expression included.
6105    """
6106
6107    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6108    return Select().from_(expression, dialect=dialect, **opts)
6109
6110
6111def column(
6112    col: str | Identifier,
6113    table: t.Optional[str | Identifier] = None,
6114    db: t.Optional[str | Identifier] = None,
6115    catalog: t.Optional[str | Identifier] = None,
6116    quoted: t.Optional[bool] = None,
6117) -> Column:
6118    """
6119    Build a Column.
6120
6121    Args:
6122        col: Column name.
6123        table: Table name.
6124        db: Database name.
6125        catalog: Catalog name.
6126        quoted: Whether to force quotes on the column's identifiers.
6127
6128    Returns:
6129        The new Column instance.
6130    """
6131    return Column(
6132        this=to_identifier(col, quoted=quoted),
6133        table=to_identifier(table, quoted=quoted),
6134        db=to_identifier(db, quoted=quoted),
6135        catalog=to_identifier(catalog, quoted=quoted),
6136    )
6137
6138
6139def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6140    """Cast an expression to a data type.
6141
6142    Example:
6143        >>> cast('x + 1', 'int').sql()
6144        'CAST(x + 1 AS INT)'
6145
6146    Args:
6147        expression: The expression to cast.
6148        to: The datatype to cast to.
6149
6150    Returns:
6151        The new Cast instance.
6152    """
6153    expression = maybe_parse(expression, **opts)
6154    data_type = DataType.build(to, **opts)
6155    expression = Cast(this=expression, to=data_type)
6156    expression.type = data_type
6157    return expression
6158
6159
6160def table_(
6161    table: Identifier | str,
6162    db: t.Optional[Identifier | str] = None,
6163    catalog: t.Optional[Identifier | str] = None,
6164    quoted: t.Optional[bool] = None,
6165    alias: t.Optional[Identifier | str] = None,
6166) -> Table:
6167    """Build a Table.
6168
6169    Args:
6170        table: Table name.
6171        db: Database name.
6172        catalog: Catalog name.
6173        quote: Whether to force quotes on the table's identifiers.
6174        alias: Table's alias.
6175
6176    Returns:
6177        The new Table instance.
6178    """
6179    return Table(
6180        this=to_identifier(table, quoted=quoted) if table else None,
6181        db=to_identifier(db, quoted=quoted) if db else None,
6182        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6183        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6184    )
6185
6186
6187def values(
6188    values: t.Iterable[t.Tuple[t.Any, ...]],
6189    alias: t.Optional[str] = None,
6190    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6191) -> Values:
6192    """Build VALUES statement.
6193
6194    Example:
6195        >>> values([(1, '2')]).sql()
6196        "VALUES (1, '2')"
6197
6198    Args:
6199        values: values statements that will be converted to SQL
6200        alias: optional alias
6201        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6202         If either are provided then an alias is also required.
6203
6204    Returns:
6205        Values: the Values expression object
6206    """
6207    if columns and not alias:
6208        raise ValueError("Alias is required when providing columns")
6209
6210    return Values(
6211        expressions=[convert(tup) for tup in values],
6212        alias=(
6213            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6214            if columns
6215            else (TableAlias(this=to_identifier(alias)) if alias else None)
6216        ),
6217    )
6218
6219
6220def var(name: t.Optional[ExpOrStr]) -> Var:
6221    """Build a SQL variable.
6222
6223    Example:
6224        >>> repr(var('x'))
6225        '(VAR this: x)'
6226
6227        >>> repr(var(column('x', table='y')))
6228        '(VAR this: x)'
6229
6230    Args:
6231        name: The name of the var or an expression who's name will become the var.
6232
6233    Returns:
6234        The new variable node.
6235    """
6236    if not name:
6237        raise ValueError("Cannot convert empty name into var.")
6238
6239    if isinstance(name, Expression):
6240        name = name.name
6241    return Var(this=name)
6242
6243
6244def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6245    """Build ALTER TABLE... RENAME... expression
6246
6247    Args:
6248        old_name: The old name of the table
6249        new_name: The new name of the table
6250
6251    Returns:
6252        Alter table expression
6253    """
6254    old_table = to_table(old_name)
6255    new_table = to_table(new_name)
6256    return AlterTable(
6257        this=old_table,
6258        actions=[
6259            RenameTable(this=new_table),
6260        ],
6261    )
6262
6263
6264def convert(value: t.Any, copy: bool = False) -> Expression:
6265    """Convert a python value into an expression object.
6266
6267    Raises an error if a conversion is not possible.
6268
6269    Args:
6270        value: A python object.
6271        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6272
6273    Returns:
6274        Expression: the equivalent expression object.
6275    """
6276    if isinstance(value, Expression):
6277        return maybe_copy(value, copy)
6278    if isinstance(value, str):
6279        return Literal.string(value)
6280    if isinstance(value, bool):
6281        return Boolean(this=value)
6282    if value is None or (isinstance(value, float) and math.isnan(value)):
6283        return NULL
6284    if isinstance(value, numbers.Number):
6285        return Literal.number(value)
6286    if isinstance(value, datetime.datetime):
6287        datetime_literal = Literal.string(
6288            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6289        )
6290        return TimeStrToTime(this=datetime_literal)
6291    if isinstance(value, datetime.date):
6292        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6293        return DateStrToDate(this=date_literal)
6294    if isinstance(value, tuple):
6295        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6296    if isinstance(value, list):
6297        return Array(expressions=[convert(v, copy=copy) for v in value])
6298    if isinstance(value, dict):
6299        return Map(
6300            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6301            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6302        )
6303    raise ValueError(f"Cannot convert {value}")
6304
6305
6306def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6307    """
6308    Replace children of an expression with the result of a lambda fun(child) -> exp.
6309    """
6310    for k, v in expression.args.items():
6311        is_list_arg = type(v) is list
6312
6313        child_nodes = v if is_list_arg else [v]
6314        new_child_nodes = []
6315
6316        for cn in child_nodes:
6317            if isinstance(cn, Expression):
6318                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6319                    new_child_nodes.append(child_node)
6320                    child_node.parent = expression
6321                    child_node.arg_key = k
6322            else:
6323                new_child_nodes.append(cn)
6324
6325        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6326
6327
6328def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6329    """
6330    Return all table names referenced through columns in an expression.
6331
6332    Example:
6333        >>> import sqlglot
6334        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6335        ['a', 'c']
6336
6337    Args:
6338        expression: expression to find table names.
6339        exclude: a table name to exclude
6340
6341    Returns:
6342        A list of unique names.
6343    """
6344    return {
6345        table
6346        for table in (column.table for column in expression.find_all(Column))
6347        if table and table != exclude
6348    }
6349
6350
6351def table_name(table: Table | str, dialect: DialectType = None) -> str:
6352    """Get the full name of a table as a string.
6353
6354    Args:
6355        table: Table expression node or string.
6356        dialect: The dialect to generate the table name for.
6357
6358    Examples:
6359        >>> from sqlglot import exp, parse_one
6360        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6361        'a.b.c'
6362
6363    Returns:
6364        The table name.
6365    """
6366
6367    table = maybe_parse(table, into=Table, dialect=dialect)
6368
6369    if not table:
6370        raise ValueError(f"Cannot parse {table}")
6371
6372    return ".".join(
6373        part.sql(dialect=dialect, identify=True)
6374        if not SAFE_IDENTIFIER_RE.match(part.name)
6375        else part.name
6376        for part in table.parts
6377    )
6378
6379
6380def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6381    """Replace all tables in expression according to the mapping.
6382
6383    Args:
6384        expression: expression node to be transformed and replaced.
6385        mapping: mapping of table names.
6386        copy: whether or not to copy the expression.
6387
6388    Examples:
6389        >>> from sqlglot import exp, parse_one
6390        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6391        'SELECT * FROM c'
6392
6393    Returns:
6394        The mapped expression.
6395    """
6396
6397    def _replace_tables(node: Expression) -> Expression:
6398        if isinstance(node, Table):
6399            new_name = mapping.get(table_name(node))
6400            if new_name:
6401                return to_table(
6402                    new_name,
6403                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6404                )
6405        return node
6406
6407    return expression.transform(_replace_tables, copy=copy)
6408
6409
6410def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6411    """Replace placeholders in an expression.
6412
6413    Args:
6414        expression: expression node to be transformed and replaced.
6415        args: positional names that will substitute unnamed placeholders in the given order.
6416        kwargs: keyword arguments that will substitute named placeholders.
6417
6418    Examples:
6419        >>> from sqlglot import exp, parse_one
6420        >>> replace_placeholders(
6421        ...     parse_one("select * from :tbl where ? = ?"),
6422        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6423        ... ).sql()
6424        "SELECT * FROM foo WHERE str_col = 'b'"
6425
6426    Returns:
6427        The mapped expression.
6428    """
6429
6430    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6431        if isinstance(node, Placeholder):
6432            if node.name:
6433                new_name = kwargs.get(node.name)
6434                if new_name:
6435                    return convert(new_name)
6436            else:
6437                try:
6438                    return convert(next(args))
6439                except StopIteration:
6440                    pass
6441        return node
6442
6443    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6444
6445
6446def expand(
6447    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6448) -> Expression:
6449    """Transforms an expression by expanding all referenced sources into subqueries.
6450
6451    Examples:
6452        >>> from sqlglot import parse_one
6453        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6454        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6455
6456        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6457        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6458
6459    Args:
6460        expression: The expression to expand.
6461        sources: A dictionary of name to Subqueryables.
6462        copy: Whether or not to copy the expression during transformation. Defaults to True.
6463
6464    Returns:
6465        The transformed expression.
6466    """
6467
6468    def _expand(node: Expression):
6469        if isinstance(node, Table):
6470            name = table_name(node)
6471            source = sources.get(name)
6472            if source:
6473                subquery = source.subquery(node.alias or name)
6474                subquery.comments = [f"source: {name}"]
6475                return subquery.transform(_expand, copy=False)
6476        return node
6477
6478    return expression.transform(_expand, copy=copy)
6479
6480
6481def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6482    """
6483    Returns a Func expression.
6484
6485    Examples:
6486        >>> func("abs", 5).sql()
6487        'ABS(5)'
6488
6489        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6490        'CAST(5 AS DOUBLE)'
6491
6492    Args:
6493        name: the name of the function to build.
6494        args: the args used to instantiate the function of interest.
6495        dialect: the source dialect.
6496        kwargs: the kwargs used to instantiate the function of interest.
6497
6498    Note:
6499        The arguments `args` and `kwargs` are mutually exclusive.
6500
6501    Returns:
6502        An instance of the function of interest, or an anonymous function, if `name` doesn't
6503        correspond to an existing `sqlglot.expressions.Func` class.
6504    """
6505    if args and kwargs:
6506        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6507
6508    from sqlglot.dialects.dialect import Dialect
6509
6510    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6511    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6512
6513    parser = Dialect.get_or_raise(dialect)().parser()
6514    from_args_list = parser.FUNCTIONS.get(name.upper())
6515
6516    if from_args_list:
6517        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6518    else:
6519        kwargs = kwargs or {"expressions": converted}
6520        function = Anonymous(this=name, **kwargs)
6521
6522    for error_message in function.error_messages(converted):
6523        raise ValueError(error_message)
6524
6525    return function
6526
6527
6528def true() -> Boolean:
6529    """
6530    Returns a true Boolean expression.
6531    """
6532    return Boolean(this=True)
6533
6534
6535def false() -> Boolean:
6536    """
6537    Returns a false Boolean expression.
6538    """
6539    return Boolean(this=False)
6540
6541
6542def null() -> Null:
6543    """
6544    Returns a Null expression.
6545    """
6546    return Null()
6547
6548
6549# TODO: deprecate this
6550TRUE = Boolean(this=True)
6551FALSE = Boolean(this=False)
6552NULL = Null()